Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version next

D toc

Excerpt

You can integrate data from other sources into your current dataset. Based on a key column that you identify in the lookup dataset, you can insert the corresponding values in other columns of the lookup dataset as new columns in your source dataset.

Tip

Tip: Column lookups are useful for adding reference data based on a column's values.

...

State-2LetterState
ALAlabama
AKAlaska
AZArizona
ARArkansas
CACalifornia
COColorado
CTConnecticut
DEDelaware
DCDistrict of Columbia
FLFlorida
GAGeorgia
HIHawaii
IDIdaho
ILIllinois
INIndiana
IAIowa
KSKansas
KYKentucky
LALouisiana
MEMaine
MDMaryland
MAMassachusetts
MIMichigan
MNMinnesota
MSMississippi
MOMissouri
MTMontana
NENebraska
NVNevada
NHNew Hampshire
NJNew Jersey
NMNew Mexico
NYNew York
NCNorth Carolina
NDNorth Dakota
OHOhio
OKOklahoma
OROregon
PAPennsylvania
RIRhode Island
SCSouth Carolina
SDSouth Dakota
TNTennessee
TXTexas
UTUtah
VTVermont
VAVirginia
WAWashington
WVWest Virginia
WIWisconsin
WYWyoming
Tip

Tip: You can download a version of this table, which also includes some timezone information. See Dict-TimezoneByState.csv.

This data table must be uploaded as a new dataset. See Import Data Page.

Perform the Lookup

Steps:

  1. In the Transformer page, click the drop-down on the column that contains your two-letter state abbreviations. Select Lookup ....
  2. In the Lookup Wizard, select the dataset to use for your lookup.
  3. For the lookup key, select the column in the dataset to use as the key value. In the above example, it is State_2Letter.
  4. Click Execute Lookup.
  5. The lookup key value is used to locate all of the other column values in the reference dataset. These values are inserted in separate columns to the immediate right of the source column.
  6. You might need to delete some of the imported columns. In the above case, you might decide to delete the two-letter state identifier column, which has been replaced by the full state name column.

...

Info

NOTE: Depending on the requirements of your target system, you can use the split transform Split transformation to break up column data so that only the numerical offset (e.g. -6:00) is present. Then, you can use the DATEDIF function to apply the timezone offset to your timestamps. In this manner, you can convert timestamps to the source timezone before they are consumed by the target system.

D s also
inCQLtrue
label(label = "enrichment_tasks")