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!

[#2]

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;

[sunny_talwar]

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;

[#4]

Great! Synthetic keys are gone

[sunny_talwar]

Awesome

[#6]

thanks!!

[#7]

thanks!!