topic Re: How to add a column with a specific value in load script? in QlikView

How to add a column with a specific value in load script?

Fri, 10 Mar 2017 15:17:53 GMT

Hi All,

I have 2 tables, A & B. with the below structures:

load

col1, col2, col3

from xxx.csv;

load

col1,col2

from yyy.csv;

I need to add an additional column in 'B' which should have the maximum value of 'col3' from A. I created a variable for that. How should I bring in the value of the variable to the column?

Max(col3) as col4 (in table B).

Ideally one single value will be populated for all the columns. Joins do not work because of the common columns. Any help?

Re: How to add a column with a specific value in load script?

hector_munoz — Fri, 10 Mar 2017 15:45:03 GMT

Hi Priyanka,

Try a LEFT JOIN:

LEFT JOIN (B)

LOAD col1,

col2,

Max(col3) AS Max_col3

RESIDENT A

GROUP BY col1,

col2;

Regards,

Re: How to add a column with a specific value in load script?

hector_munoz — Fri, 10 Mar 2017 15:46:11 GMT

If you need the maximum values of col3 of all the combinactions ob col1 and col2, just:

LEFT JOIN (B)

LOAD Max(col3) AS Max_col3

RESIDENT A;

Regards,

Re: How to add a column with a specific value in load script?

vishsaggi — Fri, 10 Mar 2017 15:47:08 GMT

Try this ?

Tab1:

load * Inline [

Col1, Col2, Col3

A, AAA, 10

B, BBB,15

C, CCC,100

D, DDD,22

];

NoConcatenate

Dummy:

LOAD Max(Col3) AS MaxCol

Resident Tab1;

LET vMaxValue = Peek('MaxCol');

Drop Table Dummy;

Tab2:

load *, '$(vMaxValue)' AS Col3A Inline [

Col1A, Col2A

A, AAA

B, BBB

C, CCC

D, DDD

];

Re: How to add a column with a specific value in load script?

Fri, 10 Mar 2017 16:09:48 GMT

but will the left join work without a common column between the tables?

Re: How to add a column with a specific value in load script?

vishsaggi — Fri, 10 Mar 2017 16:13:31 GMT

Can you share some sample data and expected output you are looking for?

Re: How to add a column with a specific value in load script?

hector_munoz — Fri, 10 Mar 2017 16:22:24 GMT

If there are no common fields in destination table and left join set, QlikView makes a cartesian product. I think that:

LEFT JOIN (B)

LOAD Max(col3) AS Max_col3

RESIDENT A;

, should work for you.

Regards,

Re: How to add a column with a specific value in load script?

Fri, 10 Mar 2017 16:36:00 GMT

Thanks Vishwa. Hector's solution worked. Cheers!

Re: How to add a column with a specific value in load script?

Fri, 10 Mar 2017 16:36:28 GMT

Thanks H. It worked