topic Re: Loading two tables as one and having a where clause in QlikView

Loading two tables as one and having a where clause

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

I have 2 tables tableA and tableB.

I want to load them together with a LEFT JOIN and return the fields from tableA while using tableB fields in the where clause.

Something like this:

tableC:
LOAD MT, MKA, moves FROM $(vPATH)\tableA.QVD (qvd);
LEFT JOIN (tableC) LOAD costart, coend, IDID FROM $(vPATH)\tableB.QVD (qvd)
WHERE (MT >= costart AND MT <= coend);

I dont want to join the tables, i just want to use tableB as parameters for tableA

Re: Loading two tables as one and having a where clause

sunny_talwar — Mon, 26 Nov 2018 12:11:29 GMT

In that case, may be all you need is to use ApplyMap

QlikView ApplyMap - Is It So Wrong?

Re: Loading two tables as one and having a where clause

marcus_sommer — Mon, 26 Nov 2018 13:46:18 GMT

I think I would go with the suggestion from Sunny but you could also use a join if you populate your start and end. It might be look like:

tableC:
LOAD MT, MKA, moves FROM $(vPATH)\tableA.QVD (qvd);
LEFT JOIN (tableC) 
LOAD costart + iterno() -1 as MT, IDID FROM $(vPATH)\tableB.QVD (qvd)
WHILE costart + iterno() - <= coend;

Whereby you will further need a common ID both loadings (and by the mapping-approach, too).

- Marcus

Re: Loading two tables as one and having a where clause

PabloMC — Mon, 26 Nov 2018 16:54:01 GMT

Hi,

You have to do it in two steps: first do the join to have all fields in one table, and then load that intermediate table using the where clause.You will need an ID field to join the tables. At the end You can only have the fields from TableA.

Something like this:

TableA:
LOAD 
ID, Field_1, Field_2, Field_3 from TableA.Qvd(qvd);
left join (TableA) 
LOAD ID, Parameter_1, Parameter_2 from TableB.qvd(qvd);

TableC:
LOAD 
ID, Field_1, Field_2, Field_3
Resident TableA
Where Field_1 > Parameter_1 and Field_2 > Parameter_2;