Differences

This shows you the differences between two versions of the page.

--- transformations:intervalmerge [2018/08/15 09:15] – created dmitry
+++ transformations:intervalmerge [2021/07/19 02:24] (current) – [Examples] craigt
@@ Line 1: / Line 1: @@
-===== Interval merge =====
+{{ transformations:IntervalMergeAction.png}}
+====== INTERVAL MERGE ======
+Category: Transform / Advanced \\
+\\
+=====Description=====
 This action merges two tables based on whether a value in one table falls into a range specified by two values in another table.
-**Example**
+The lower boundary of a range is inclusive, the upper is exclusive. Ranges can overlap. In such cases, one row for each match will be inserted into the resulting table.\\
-Table 1: Boundaries
+\\
-^ Lower ^ Upper ^ Feels ^
+=====Use cases=====
-|      0|     15|Cold    |
+//Interval merge// can be used to to categorize or "bucket" values in a dataset that fall within a given range or scale, such as survey responses (1-3 = "bad", 4-6 = "neutral", 7-10 = "good"), temperature ranges, size scales, etc.
-|     15|     25|Comfortable |
-|     25|     99|Hot   |
-Table 2: Temperature in Toronto
+This action is a good substitute for a [[transformations:lookup|Lookup]] action that uses multiple lookup values with similar return values (e.g., 1="good", 2="good", 3="neutral", 4="bad", 5="bad).
-^Month	^High, C^
+\\
+=====Action settings=====
+^Setting^Description^
+|Merge table|Select the table to merge with the current dataset.|
+|Columns|Select whether to merge all columns in the second dataset, or just selected columns.  Options:  //All columns// or\\ //Selected columns// (choose which columns to include in the merge).|
+The reference to the second table will appear as a dotted line connecting this action to the second dataset in the application window.\\
+\\
+=====Remarks=====
+To create a lookup set that doesn't contain any "gaps" be sure to set the "less than" value one higher than the last value you want to be included in the range, and start the //following// pairing with that same value.
+^Low  ^High  ^Label  ^
+|  0|  4| Range is 0 to __3__|
+|  4|  8| Range is 4 to __7__ (don't start with "5" or you'll lose "4")|
+|  8|  11| Range is 8 to __10__. (range end is "10" as "11" is excluded)|
+\\
+A lookup pair (low, high) cannot have a "high" value that is equal to the "low" value.  This pairing will not return a match as there is no upper threshold.  e.g. with "1" as the low value and "1" (//exclusive//) as the high value, no actual upper threshold has been defined.\\
+\\
+//Interval merge// can also use text-based lookup ranges and values (see Example 2, below).\\
+\\
+=====Examples=====
+**Example 1:**  Find what it feels like in Toronto during the year.\\
+**Table 1:** Temperature in Toronto (Celsius)
+^Month	^Temp High (C)^
 |Jan	|   0|
 |Feb	|   0|
@@ Line 26: / Line 55: @@
 |Dec	|   3|
-Goal: find what it feels like in Toronto during a year.
+**Table 2:** Boundaries
+^ Lower ^ Upper ^ Feels ^
-Solution: merge using the "Interval merge" action where [Temperature in Toronto] is between [Lower] and [Upper].
+|      0|     15|Cold    |
+|     15|     25|Comfortable |
-Table: Result
+|     25|     99|Hot   |
+\\
+**Action parameters:**
+> Table to merge is "Table 2"
+> Values in column (Table 1) is "Temp High (C)"
+> Are greater than or equal to "Lower" (in Table 2)
+> And less than "Upper" (in Table 2)
+\\
+**Result table:**
 ^Month	^High, C^ Feels ^
 |Jan	|   0|Cold  |
@@ Line 45: / Line 82: @@
 |Dec	|   3|Cold  |
+\\
+\\
+**Example 2:** Use a letter-based merge to assign Names to letter ranges.
+**Table 1:** Names
+^Name  ^
+|Mary  |
+|Sally  |
+|Bob  |
+|Max  |
+|Bruce  |
+|Tammy  |
+|Chris  |
+|Frank  |
+|Zeek  |
+**Table 2:** Lookup ranges
+^Low  ^High  ^Label  ^
+|A  |F  |Range A-E  |
+|F  |L  |Range F-K  |
+|L  |S  |Range L-R  |
+|S  |Z  |Range S-Y  |
+|Z  |Zz  |Range Z  |
+The high value of "Zz" is used in the last record since the low value cannot be the\\ same as the high value.  See //Remarks//, above.\\
+\\
+**Action parameters:**
+> Table to merge is "Table 2"
+> Values in column "Name" (Table 1)
+> Are greater or equal to "Low" (Table 2)
+> And less than "High" (Table 2)
+> Columns to merge is "Label"
+\\
+**Result table:**
+^Name  ^Label  ^
+|Mary  |Range L-R  |
+|Sally  |Range S-Y  |
+|Bob  |Range A-E  |
+|Max  |Range L-R  |
+|Bruce  |Range A-E  |
+|Tammy  |Range S-Y  |
+|Chris  |Range A-E  |
+|Frank  |Range F-K  |
+|Zeek  |Range Z  |
+\\
+====Community examples====
+  * [[https://community.easymorph.com/t/rules-on-2-or-more-tables/952|Rules on 2 or more tables (with downloadable example)]]
+  * [[https://community.easymorph.com/t//1851/1|Example of using Interval Merge]] ([[https://community.easymorph.com/uploads/short-url/3ROZA38vhs9S7bXLcNKPIjudF61.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Table 1//; Action position: //2//)
+  * [[https://community.easymorph.com/t//2292/2|Assign value from conditions based on a table]] ([[https://community.easymorph.com/uploads/short-url/hsFachkdfbcKFoA2ZK8WcmOUfA9.morph|Project]]; Module: //Main//; Group: //Group 1//; Table: //Result//; Action position: //2//)
+\\
+=====See also=====
+  * [[transformations:lookup|Lookup]]
+  * [[transformations:merge|Merge another table]]
+  * [[transformations:crossmerge|Cross merge]]