Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a main table, and then want to derive a new field based on a column. The main table comes from different spreadsheets and the result is something as below
step | DataColumn |
A-1 | data1 |
B-2 | data2 |
B-3 | data1 |
B-4 | data3 |
C-5 | data4 |
A-1 | data4 |
B-2 | data4 |
B-3 | data3 |
B-4 | data1 |
C-5 | data1 |
A-1 | data2 |
Now I want to add another column after, based on DataColumn, if DataColumn is data1 or data2, then new column should be Data1or2, DataColumn is data3 or data4 then new column should be Data3or4. I should use a preceding load and create a Temp Table and then join it to my main table - should be so easy but can;t get it to work.
step | DataColumn | New Column |
A-1 | data1 | Data1or2 |
B-2 | data2 | Data1or2 |
B-3 | data1 | Data1or2 |
B-4 | data3 | Data3or4 |
C-5 | data4 | Data3or4 |
A-1 | data4 | Data3or4 |
B-2 | data4 | Data3or4 |
B-3 | data3 | Data3or4 |
B-4 | data1 | Data1or2 |
C-5 | data1 | Data1or2 |
A-1 | data2 | Data1or2 |
Thanks!
Not able to share the whole script
but this is basically what I'm doing
MainTable:
Crosstable(A,B)
Load....From
Crosstable(A,B)
Load...From
Crosstable(A,B)
Load...From
(for different data sources)
Then I have my MainTable built, basically same as I showed you.
Then I've added the following, which gives me synthetic key
LOAD step,
DataColumn,
If(Match(DataColumn, 'data1', 'data2'), 'Data1or2', 'Data3or4') as [New Column]
RESIDENT MainTable;
Give this a shot:
MainTable:
Crosstable(A,B)
Load....From
Crosstable(A,B)
Load...From
Crosstable(A,B)
Load...From
(for different data sources)
FINALMainTable:
LOAD *,
If(Match(DataColumn, 'data1', 'data2'), 'Data1or2', 'Data3or4') as [New Column]
RESIDENT MainTable;
DROP Table MainTable;
Great! Synthetic keys are gone
Awesome
thanks!!
thanks!!