Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
yipchunyu
Creator
Creator

JOIN tables in script loading

I just learning to use binary load.  I want to export tables from the loading scripts (instead of click export from objects).

However, it seems harder than my expectations.

For example, below 

OutputRaw:
NoConcatenate
LOAD
Field A,
Field B,
Field C
Resident TableA;
Join (OutputRaw)
Load
Field B,
FieldDate
Resident TableB
;

OutputFinal:
NoConcatenate
LOAD *
Resident OutputRaw;
Join (OutputFinal)
FieldDate,
FieldYear
Resident Calendar
Where FieldYear ='2020';

STORE OutputFinal into [C:\New.csv](txt);
DROP Table OutputRaw;
Drop Table OutputFinal;

Actually, it's very simple if using SQL.  I just want to join the table B and Table C and then use table C's Field Year to filter up the required data.

Table A Table B Table C
Field A    
Field B->Field B  
Field C    
  Field Date->Field Date
    Field Year


Any idea to make it much simple?

1 Solution

Accepted Solutions
Vegar
MVP
MVP

There are four ways of doing join in QlikView

JOIN LOAD //Full outer join

LEFT JOIN //Keep all values in orignal table 

RIGHT JOIN //Keep all values in the join table

INNER JOIN //Keep only where there is a match in both tables

 

Try my script below and laborate with the different join methods to get your desired result. 

TableA:
LOAD * Inline [
Field A,Field B,Field C
A, BB, CCC
AA, BBB,C
AAA,B, CC
];

TableB:
LOAD * Inline [
Field B, FieldDate
B, 2020-04-30
BB, 2020-01-31
BBB, 2019-12-31
BBBB, 2020-03-31
];

TableC:
LOAD
dayname(FieldDate) as FieldDate,
year(FieldDate) as FieldYear
Inline [
FieldDate
2020-05-31
2020-04-30
2020-03-31
2020-02-29
2020-01-31
2019-12-31
];

 

Output:
NoConcatenate
LOAD
[Field A],
[Field B],
[Field C]
Resident TableA;

inner Join (Output)
Load
[Field B],
FieldDate
Resident TableB
;
inner JOIN (Output)
LOAD
FieldDate,
FieldYear
Resident TableC
Where FieldYear ='2020';

STORE Output into [C:\New.csv](txt);

DROP TABLE Output;

 

View solution in original post

2 Replies
Vegar
MVP
MVP

There are four ways of doing join in QlikView

JOIN LOAD //Full outer join

LEFT JOIN //Keep all values in orignal table 

RIGHT JOIN //Keep all values in the join table

INNER JOIN //Keep only where there is a match in both tables

 

Try my script below and laborate with the different join methods to get your desired result. 

TableA:
LOAD * Inline [
Field A,Field B,Field C
A, BB, CCC
AA, BBB,C
AAA,B, CC
];

TableB:
LOAD * Inline [
Field B, FieldDate
B, 2020-04-30
BB, 2020-01-31
BBB, 2019-12-31
BBBB, 2020-03-31
];

TableC:
LOAD
dayname(FieldDate) as FieldDate,
year(FieldDate) as FieldYear
Inline [
FieldDate
2020-05-31
2020-04-30
2020-03-31
2020-02-29
2020-01-31
2019-12-31
];

 

Output:
NoConcatenate
LOAD
[Field A],
[Field B],
[Field C]
Resident TableA;

inner Join (Output)
Load
[Field B],
FieldDate
Resident TableB
;
inner JOIN (Output)
LOAD
FieldDate,
FieldYear
Resident TableC
Where FieldYear ='2020';

STORE Output into [C:\New.csv](txt);

DROP TABLE Output;

 

yipchunyu
Creator
Creator
Author

Great thx.  Your explanation is very clean.  So, I can simply join tables multiple times as your working example.  Thx.