मेरे पास डेटा के 2 सेट हैं जिन पर मैं बाहरी जुड़ना चाहता हूं (मूल रूप से दोनों सेटों से सभी डेटा शामिल करें, खाली कक्षों के साथ जहां डेटा एक सेट में है लेकिन दूसरा नहीं)

मैंने देखा है Google शीट में टेबल से जुड़ें - पूरा जॉइन करें तथा Google पत्रक बाहरी रूप से 2 टेबल पर जुड़ते हैं जिन्हें संक्षेप में प्रस्तुत किया जाता है ए>

लेकिन मैं अपना काम नहीं कर सकता।

मैंने अपना डेटा तब तक सरल कर दिया है जब तक कि मैं यह काम नहीं कर सकता, लेकिन मूल रूप से दोनों डेटासेट में साल और महीना होता है (जिसे मैं एक आईडी बनाने के लिए जोड़ता हूं, साल और महीना प्रत्येक डेटासेट में केवल एक बार दिखाई देता है।

1st Screenshot

मेरा आउटपुट हरे रंग में हाइलाइट किए गए डेटासेट जैसा दिखना चाहिए (मैंने मैन्युअल रूप से टाइप किया)

लेकिन मुझे अपने फॉर्मूले से जो कुछ मिलता है वह या तो एक त्रुटि है (जैसे पहले स्क्रीनशॉट में) या #VALUE का भार!

2nd screenshot

यह मेरी नमूना पत्रक का लिंक है। https://docs.google.com/spreadsheets/d/ 1Iyhi7WKAA6g0hWpgl33fOe8q78MtzsATq4khnqcAT-w/edit?usp=sharing

यह मुझे पागल कर रहा है, क्योंकि मैं पूरी तरह से समझ नहीं पा रहा हूं कि सरणी सूत्र कैसे काम कर रहा है

1
Michael Liew 18 अप्रैल 2021, 15:07

1 उत्तर

Google QUERY () फ़ंक्शन बहुत शक्तिशाली है और SQL के बाद तैयार किया गया है लेकिन यह पूर्ण कार्यान्वयन नहीं है। इसलिए, मैंने SQL जॉइन, इनर, लेफ्ट, राइट और फुल को अनुकरण करने के लिए एक कस्टम फ़ंक्शन लिखने का निर्णय लिया।

const ss = SpreadsheetApp.getActiveSpreadsheet();

/**
 * Combines two ranges with a common key and can be used standalone or with the QUERY() function to simulate joins.
 *
 * 
 * @constructor
 * @param {(string|array)} range1 - the main table as a named range, a1Notation or an array
 * @param {(string|array)} range2 - the related table as a named range, a1Notation or an array
 * @param {number} primaryKey - the unique identifier for the main table, columns start with "1"
 * @param {number} foreignKey - the key in the related table to join to the main table, columns start with "1"
 * @param {string} joinType, type of join - "Inner", "Left", "Right", "Full", optional and defaults to "Inner", case insensitive
 * @returns {array} array results as a two dimensional array
 * @customfunction
 *
 * Result Set Example:
 *
 * =QUERY(denormalize("Employees","Orders",1,3), "SELECT * WHERE Col2 = 'Davolio' AND Col8=2", FALSE)
 *
 * |EmpID|LastName|FirstName|OrderID|CustomerID|EmpID|OrderDate|ShipperID|
 * |:----|:-------|:--------|:------|:---------|:----|:--------|:--------|
 * |1      |Davolio |Nancy    |10285  |63        |1    |8/20/1996|2        |
 * |1    |Davolio |Nancy    |10292  |81        |1    |8/28/1996|2        |
 * |1    |Davolio |Nancy    |10304  |80        |1    |9/12/1996|2        | 
 * etc.
 *
 * Other Examples:
 * =denormalize("Employees","Orders",1,3)
 * =denormalize("Employees","Orders",1,3,"full")
 * =QUERY(denormalize("Employees","Orders",1,3,"left"), "SELECT * ", FALSE)
 * =QUERY(denormalize("Employees","Orders",1,3), "SELECT * WHERE Col2 = 'Davolio'", FALSE)
 * =QUERY(denormalize("Employees","Orders",1,3), "SELECT * WHERE Col2 = 'Davolio' AND Col8=2", FALSE)
 * =denormalize("Orders","OrderDetails",1,2)
 * // multiple joins 
 * =denormalize("Employees",denormalize("Orders","OrderDetails",1,2),1,3)
 * =QUERY(denormalize("Employees",denormalize("Orders","OrderDetails",1,2),1,3), "SELECT *", FALSE)
 * =denormalize(denormalize("Employees","Orders",1,3),"OrderDetails",1,2)
 * =QUERY(denormalize("Employees",denormalize("Orders","OrderDetails",1,2),1,3), "SELECT *", FALSE)
 * =QUERY(denormalize(denormalize("Employees","Orders",1,3),"OrderDetails",4,2), "SELECT *", FALSE)
 *
 * Joins Types:
 * (INNER) JOIN: Returns records that have matching values in both tables
 * LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
 * RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
 * FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
 *
 * Note: the most common join is INNER which is why that is the default join type
 * 
 * General:
 * This alogithm is more efficient than using nested loops and uses a form of a hash table instead.
 * A hash table is a structure that can map index keys to values and typically resembles something like this:
 * [index][values]
 *
 * Since javascript provides a native function to return the index, there is no need to store it so,
 * this hash table only stores the values.
 *
 * There is minimal testing in DENORMALIZE() to validate parameters.
 *
 * Author/Coder/Tester: John Agusta, 03/28/2021, Raleigh, NC USA
 *
 * License: Follows the GNU General Public License (GNU GPL or simply GPL), a series of widely-used free 
 * software licenses that guarantee end users the freedom to run, study, share, and modify the software.
 *
 * http://www.gnu.org/licenses/gpl.html
 *
 *
 * Note: DENORMALIZE() can simulate multiple joins by nesting DENORMALIZE() functions as needed.
 *
 * Recursion is theoretically possible to unlimited depth, although only a few levels are normally used in practical programs
 * as performance will degrade accordingly.
 *
 * DENORMALIZE(range1, range2, primaryKey, foreignKey, joinType)
 *
 */
function DENORMALIZE(range1, range2, primaryKey, foreignKey, joinType) {
  var i = 0;
  var j = 0;
  var index = -1;
  var lFound = false;
  var aDenorm = [];
  var hashtable = [];
  var aRange1 = "";
  var aRange2 = "";
  joinType = DefaultTo(joinType, "INNER").toUpperCase();
  // the 6 lines below are used for debugging
  //range1 = "Employees";
  //range1 = "Employees!A2:C12";
  //range2 = "Orders";
  //primaryKey = 1;
  //foreignKey = 3;
  //joinType = "LEFT";
  // Sheets starts numbering columns starting with "1", arrays are zero-based
  primaryKey -= 1;
  foreignKey -= 1;
  // check if range is not an array
  if (typeof range1 !== 'object') {
    // Determine if range is a1Notation and load data into an array
    if (range1.indexOf(":") !== -1) {
      aRange1 = ss.getRange(range1).getValues();
    } else {
      aRange1 = ss.getRangeByName(range1).getValues();
    } 
  } else {
    aRange1 = range1;
  }
  
  if (typeof range2 !== 'object') {
    if (range2.indexOf(":") !== -1) {
      aRange2 = ss.getRange(range2).getValues();
    } else {
      aRange2 = ss.getRangeByName(range2).getValues();
    }
  } else {
    aRange2 = range2;
  }
  
  // make similar structured temp arrays with NULL elements
  var tArray1 = MakeArray(aRange1[0].length);
  var tArray2 = MakeArray(aRange2[0].length);
  var lenRange1 = aRange1.length;
  var lenRange2 = aRange2.length;
  hashtable = getHT(aRange1, lenRange1, primaryKey);
  for(i = 0; i < lenRange2; i++)  {
    index = hashtable.indexOf(aRange2[i][foreignKey]);
    if (index !== -1) {
      aDenorm.push(aRange1[index].concat(aRange2[i]));
    }
  }
  // add left and full no matches
  if (joinType == "LEFT" || joinType == "FULL") {
    for(i = 0; i < lenRange1; i++)  {
      index = aDenorm.indexOf(aRange1[i][primaryKey]);
      //index = aScan(aDenorm, aRange1[i][primaryKey], primaryKey)
      if (index == -1) {
        aDenorm.push(aRange1[i].concat(tArray2));
      }
    }
  }
  // add right and full no matches
  if (joinType == "RIGHT" || joinType == "FULL") {
    for(i = 0; i < lenRange2; i++)  {
      index = ASCAN(aDenorm, aRange2[i][foreignKey], primaryKey)
      if (index == -1) {
        aDenorm.push(tArray1.concat(aRange2[i]));
      }
    }
  }
    return aDenorm;
}

function getHT(aRange, lenRange, key){
var aHashtable = [];
var i = 0;
for (i=0; i < lenRange; i++ ) {
  //aHashtable.push([aRange[i][key], i]);
  aHashtable.push(aRange[i][key]);
  }
return aHashtable;
}

function MakeArray(length) {
  var i = 0;
  var retArray = [];
  for (i=0; i < length; i++) {
    retArray.push("");
  }
  return retArray;
}

function DefaultTo(valueToCheck, valueToDefault) {
return typeof valueToCheck === "undefined" ? valueToDefault : valueToCheck;
}

/**
 *
 * Search a multi-dimensional array for a value and return either the index or value if found, -1 or an empty sting otherwise
 * @constructor
 * @param {array} aValues - the array to scan
 * @param {string} searchVal - the value to look for
 * @param {number} searchCol - the array column to search
 * @param {number} returnCol - optional, the array column to return if specified, otherwise array index is returned
 * @returns {(number|value)} array index of value found or array value specified by returnCol
 * @customfunction
 */
function ASCAN(aValues, searchVal, searchCol, returnCol) {
var retval = typeof returnCol === "undefined" ? -1 : "";
var i = 0;
var aLen   = aValues.length;
for (i = 0; i < aLen; i++) {
    if (aValues[i][searchCol] == searchVal) {
        retval = typeof returnCol === "undefined" ? i : aValues[i][returnCol];
        break;
    }
}
return retval;
}

मेरे पास यहां उदाहरणों के साथ एक शीट है:

https://script.google.com/home/projects/1aQDY3Y0rOj0VrViLffYfARP9rp2j9jQ0XpUcFvye8XnxvkHy3Qr6_d0_/edit
0
JohnA 18 अप्रैल 2021, 14:52