Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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
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
];
but will the left join work without a common column between the tables?
Can you share some sample data and expected output you are looking for?
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
Thanks Vishwa. Hector's solution worked. Cheers!
Thanks H. It worked