New column in load script

[#1]

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!

[sunny_talwar · Accepted solution]

This did not work:

LOAD step,

          DataColumn,

         If(Match(DataColumn, 'data1', 'data2'), 'Data1or2', 'Data3or4') as [New Column]

FROM ....

[krishnacbe]

Hi,

Hope the attached qvw helps.

Regards

Krishnapriya

[#4]

Thanks! This works

[trdandamudi]

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

];

[#6]

Now it gives me synthetic keys, is it OK?

[sunny_talwar]

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

[#8]

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?

[krishnacbe]

Hi,

Please check the sample I have attached. It doesn't have synthetic key.

[sunny_talwar]

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