User Tools

Site Tools


transformations:lookup

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
transformations:lookup [2018/08/15 14:21] dmitrytransformations:lookup [2021/07/19 02:31] (current) – [Examples] craigt
Line 1: Line 1:
-===== Lookup =====+{{ transformations:LookupAction.png}} 
 +====== LOOKUP ====== 
 +Category: Transform / Basic\\
  
-This action looks up values from a specified column in another table andif found, returns value from the same row but different column of that table.+\\  
 +=====Description===== 
 +This action looks up values from a specified column in another table and if found, returns value from the same row but different column of that table.
  
-When value is not found, three options are available:+When the value is not found, three options are available:
   * Keep old value   * Keep old value
   * Leave cell empty   * Leave cell empty
   * Put some predefined (default) value   * Put some predefined (default) value
  
-EXAMPLE+\\ 
 +=====Use cases===== 
 +  * Used to replace common misspellings with the correct value. 
 +  * Used to replace abbreviations with the full value. 
 +  * Used to replace numeric codes with descriptive text values.
  
-**Table 1:** Canadian Olympic Champions 2014+\\  
 +=====Action settings===== 
 +^Setting^Description^ 
 +|Lookup table|Select the dataset that will serve as the lookup table.| 
 +\\  
 +====Mode settings==== 
 +^Setting^Description^ 
 +|Append new column|A new column (named "New name") will be created in the current dataset to hold the result of the lookup.| 
 +|Replace in (column)|Upon a successful match in the lookup table, the return value will //replace// the original value in the current dataset.| 
 +\\  
 +====Columns that should match settings==== 
 +^Setting^Description^ 
 +|This table|Select the column in the current dataset containing values that will be matched in the foreign dataset.| 
 +|(Look up in column)|The column in the second dataset that will be used to find matching values in the current dataset.| 
 +|Return from|Select the column in the second table from which values will be returned based on a match in the lookup column.| 
 +\\  
 +====No conditions met options==== 
 +^Setting^Description^ 
 +|Keep original value|If a value does not have a match in the lookup table, keep the original value.| 
 +|Make empty|If a value does not have a match in the lookup table, make the value //empty//.| 
 +|Default to|Options //it's an expression// or //it's a text or number// Enter either an expression to be evaluated or a static value\\ to be used in cases where a value is not found in the lookup table.|
  
-^  Gold medalist  ^  Province  ^  Sport  ^ +\\  
-| Dara Howell  |  Ontario  |  women’s slopestyle +=====Examples=====
-| Justine Dufour-Lapointe  |  Quebec  |  women’s moguls +
-| Kaillie Humphries  |  Alberta  |  women’s bobsleigh +
-| Alex Bilodeau  |  QC  |  men’s moguls +
-| Heather Moyse  |  P.E.I.  |  women’s bobsleigh +
-| Charles Hamelin  |  Que.  |  men’s short-track  |+
  
-**Objective:** Substitute all instances of the province name deviations with "Quebec" for consistency. +**Example:** Substitute all instances of the province name variations with "Quebec" for consistency. 
  
-**Table 2:** Substitution look-up table+**Source table 1:** Canadian Olympic Champions 2014
  
- Old name  ^  New name    +^Gold medalist  ^Province  ^Sport  ^ 
-| Quebec  |  Quebec    +| Dara Howell  |Ontario  |women’s slopestyle  | 
-| QC  |  Quebec  |  +| Justine Dufour-Lapointe  |Quebec  |women’s moguls  
-| Que.  |  Quebec  +| Kaillie Humphries  |Alberta  |women’s bobsleigh  | 
 +| Alex Bilodeau  |QC  |men’s moguls  
 +| Heather Moyse  |P.E.I.  |women’s bobsleigh  | 
 +| Charles Hamelin  |Que.  |men’s short-track  | 
 +\\  
 +**Source table 2:** Substitution lookup table
  
-**Transformation:** +^Old name  ^New name  ^   
-  Replace value in column: Province +| Quebec  |Quebec  |   
-  Using table: Table +| QC  |Quebec  |  
-  Look up in column: Old name +| Que.  |Quebec  |  
-  Replace with values from column: New name +\\  
-  When not found: Keep original value+**Action parameters:** 
 +Replace value in column: Province 
 +>Using table: Source table 
 +>Look up in column: Old name 
 +>Replace with values from column: New name 
 +>When not found: Keep original value 
 +\\  
 +**Result table:**
  
-**Result:**+^Gold medalist  ^Province  ^Sport 
 +| Dara Howell  |Ontario  |women’s slopestyle 
 +| Justine Dufour-Lapointe  |Quebec  |women’s moguls 
 +| Kaillie Humphries  |Alberta  |women’s bobsleigh 
 +| Alex Bilodeau  |Quebec  |men’s moguls 
 +| Heather Moyse  |P.E.I.  |women’s bobsleigh 
 +| Charles Hamelin  |Quebec  |men’s short-track 
 +\\  
 +**Alternate result:**\\ 
 +If "Append new column" had been selected instead of "Replace value", above, the following dataset would be the output: 
 + 
 +^Gold medalist  ^Province  ^Sport  ^New name^ 
 +| Dara Howell  |Ontario  |women’s slopestyle  |Ontario 
 +| Justine Dufour-Lapointe  |Quebec  |women’s moguls  |Quebec 
 +| Kaillie Humphries  |Alberta  |women’s bobsleigh  |Alberta 
 +| Alex Bilodeau  |QC  |men’s moguls  |Quebec 
 +| Heather Moyse  |P.E.I.  |women’s bobsleigh  |P.E.I. 
 +| Charles Hamelin  |Que.  |men’s short-track  |Quebec 
 + 
 +\\  
 +====Community examples==== 
 +  * [[https://community.easymorph.com/t//1551/2|How to look up and replace a string of text]] ([[https://community.easymorph.com/uploads/short-url/dvCtD7gg0AHUtXZ7tmQmuHflSYU.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Table 3//; Action position: //4//) 
 +  * [[https://community.easymorph.com/t//1837/4|Joining different pairs of value equivalences into “global equivalences”]] ([[https://community.easymorph.com/uploads/short-url/1TpBXIjf6r4auDHlOBejKh2rSTw.morph|Project]]; Module: //Group//; Group: //Tab 1//; Table: //Result//;\\ Action position//2//) 
 +  [[https://community.easymorph.com/t//2060/2|Search, remove, and shift]] ([[https://community.easymorph.com/uploads/short-url/cCqOTak9SLKPXbityR5IAlgULJa.morph|Project]]; Module: //Main//; Group: //Regrouping//; Table: //ExtractingData.xlsx//; Action position: //12//) 
 + 
 +\\  
 +=====See also===== 
 +  [[transformations:merge|Merge another table]] 
 +  * [[transformations:replacecolumns|Modify columns(s)]] 
 +  * [[transformations:rule|Rule]]
  
-^  Gold medalist  ^  Sport  ^  Province  ^ 
-| Dara Howell  |  women’s slopestyle  |  Ontario  | 
-| Justine Dufour-Lapointe  |  women’s moguls  |  Quebec  | 
-| Kaillie Humphries  |  women’s bobsleigh  |  Alberta  | 
-| Alex Bilodeau  |  men’s moguls  |  Quebec  | 
-| Heather Moyse  |  women’s bobsleigh  |  P.E.I.  | 
-| Charles Hamelin  |  men’s short-track  |  Quebec  | 
-  
transformations/lookup.1534357279.txt.gz · Last modified: 2018/08/15 14:21 by dmitry

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki