topic Re: JOIN tables in script loading in QlikView

JOIN tables in script loading

yipchunyu — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: JOIN tables in script loading

Vegar — Tue, 30 Apr 2019 09:01:42 GMT

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;

Re: JOIN tables in script loading

yipchunyu — Tue, 30 Apr 2019 10:08:03 GMT

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