Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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?

1 Solution

Accepted Solutions
hector_munoz
Specialist
Specialist

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

View solution in original post

8 Replies
hector_munoz
Specialist
Specialist

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
Specialist
Specialist

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
Champion III
Champion III

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

];

Not applicable
Author

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

vishsaggi
Champion III
Champion III

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

hector_munoz
Specialist
Specialist

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

Not applicable
Author

Thanks Vishwa. Hector's solution worked. Cheers!

Not applicable
Author

Thanks H. It worked