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!
This did not work:
LOAD step,
DataColumn,
If(Match(DataColumn, 'data1', 'data2'), 'Data1or2', 'Data3or4') as [New Column]
FROM ....
This did not work:
LOAD step,
DataColumn,
If(Match(DataColumn, 'data1', 'data2'), 'Data1or2', 'Data3or4') as [New Column]
FROM ....
Hi,
Hope the attached qvw helps.
Regards
Krishnapriya
Thanks! This works
May be as attached:
Data:
Load
Step,
DataColumn,
If(DataColumn='data1' OR DataColumn='data2','Data1or2','Data3or4') as NewColumn
Inline [
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 it gives me synthetic keys, is it OK?
It should not give you synthetic keys. Are you creating this in a resident load? I would suggest you to create it in the first load when you are loading it from your database
Yes I have to use Resident because my main table is coming from different sources...Can you advise please on how I can use resident load and not get synthetic keys? or maybe use a preceding load?
Hi,
Please check the sample I have attached. It doesn't have synthetic key.
Would you be able to share the script you are using? We might be able to suggest you the best alternative once we see what exactly do you have