topic Re: New column in load script in QlikView

New column in load script

Mon, 13 Jun 2016 15:02:55 GMT

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!

Re: New column in load script

sunny_talwar — Mon, 13 Jun 2016 15:13:44 GMT

This did not work:

LOAD step,

DataColumn,

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

FROM ....

Re: New column in load script

krishnacbe — Mon, 13 Jun 2016 15:15:56 GMT

Hi,

Hope the attached qvw helps.

Regards

Krishnapriya

Re: New column in load script

Mon, 13 Jun 2016 15:33:38 GMT

Thanks! This works

Re: New column in load script

trdandamudi — Mon, 13 Jun 2016 15:35:19 GMT

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

];

Re: New column in load script

Mon, 13 Jun 2016 15:48:55 GMT

Now it gives me synthetic keys, is it OK?

Re: New column in load script

sunny_talwar — Mon, 13 Jun 2016 15:50:53 GMT

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

Re: New column in load script

Mon, 13 Jun 2016 15:59:16 GMT

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?

Re: New column in load script

krishnacbe — Mon, 13 Jun 2016 16:00:14 GMT

Hi,

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

Re: New column in load script

sunny_talwar — Mon, 13 Jun 2016 16:01:38 GMT

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

Re: New column in load script

Mon, 13 Jun 2016 16:08:54 GMT

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;

Re: New column in load script

sunny_talwar — Mon, 13 Jun 2016 16:11:30 GMT

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;

Re: New column in load script

Mon, 13 Jun 2016 16:18:53 GMT

Great! Synthetic keys are gone

Re: New column in load script

sunny_talwar — Mon, 13 Jun 2016 16:19:24 GMT

Awesome

Re: New column in load script

Mon, 13 Jun 2016 16:22:19 GMT

thanks!!

Re: New column in load script

Mon, 13 Jun 2016 16:22:26 GMT

thanks!!