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

[#1]

Hi All,

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

A:

load

col1, col2, col3

from xxx.csv;

B:

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?

[hector_munoz]

Hi Priyanka,

Try a LEFT JOIN:

LEFT JOIN (B)

LOAD col1,

          col2,

          Max(col3)     AS Max_col3

RESIDENT A

GROUP BY col1,

                    col2;

Regards,

H

[hector_munoz]

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,

H

[vishsaggi]

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

];

[#5]

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

[vishsaggi]

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

[hector_munoz · Accepted solution]

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,

H

[#8]

Thanks Vishwa. Hector's solution worked. Cheers!

[#9]

Thanks H. It worked