topic Re: Two left joins only first left join bringing back results in QlikView

Two left joins only first left join bringing back results

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

Hi,

I have the below script

Visit:

LOAD

Db&'_'&Id as [%ID PK]

,Visitkey

,Customer

FROM

[..\02_QVD\S1-VISIT.qvd]

(qvd);

LEFT JOIN

LOAD

Db&'_'&Id as as [%ID PK]

,Area

FROM

[..\02_QVD\S1-OPEN.qvd]

(qvd);

LEFT JOIN

LOAD

Db&'_'&Id as [%ID PK]

,Area

FROM

[..\02_QVD\S1-Closed.qvd]

(qvd);

DROP Field [%ID PK];

The results are returning back for the QVD S1-Open but not for the QVD S1-Closed .

Any ideas?

Thanks

Re: Two left joins only first left join bringing back results

vishsaggi — Tue, 11 Jul 2017 20:54:35 GMT

You definitely have data in S1-Closed right? Can you do this?

Visit:

LOAD

Db&'_'&Id as [%ID PK]

,Visitkey

,Customer

FROM

[..\02_QVD\S1-VISIT.qvd]

(qvd);

LEFT JOIN

LOAD

Db&'_'&Id as as [%ID PK]

,Area

FROM

[..\02_QVD\S1-OPEN.qvd](qvd);

NoConcatenate

Final:

LOAD *

RESIDENT Visit;

LEFT JOIN (Final)

LOAD

Db&'_'&Id as [%ID PK]

,Area

FROM

[..\02_QVD\S1-Closed.qvd](qvd);

Drop Table Visit;

Drop Field [%ID PK];

Re: Two left joins only first left join bringing back results

marcginqo — Tue, 11 Jul 2017 21:21:41 GMT

Hi Ivan,

the S1-Closed does not give you any results because what qlik does is it will do the join on all similar fields already in the table.

So the first left join will add the Area field to the table. Then the second left join (the S1-Closed.qvd) will do the join on the [%ID PK] and the Area field. What you should do is alias the Area field in the second left join.

So the script will be like:

Visit:

LOAD

Db&'_'&Id as [%ID PK]

,Visitkey

,Customer

FROM

[..\02_QVD\S1-VISIT.qvd]

(qvd);

LEFT JOIN

LOAD

Db&'_'&Id as as [%ID PK]

,Area

FROM

[..\02_QVD\S1-OPEN.qvd]

(qvd);

LEFT JOIN

LOAD

Db&'_'&Id as [%ID PK]

,Area AS AreaClosed

FROM

[..\02_QVD\S1-Closed.qvd]

(qvd);

DROP Field [%ID PK];

Now you could do a resident load of the Visit table as folows:

NoConcatenate

VisitNew:

LOAD

[%ID PK]

,Visitkey

,Customer

,IF(Not IsNull(Area), Area, AreaClosed) AS Area

Resident Visit;

Hope this helps,

Marc

Re: Two left joins only first left join bringing back results

ivandrago — Tue, 11 Jul 2017 21:42:14 GMT

Yes I do have data in there.

Unfortunately the above has not worked?

Re: Two left joins only first left join bringing back results

venkatraju — Tue, 11 Jul 2017 21:49:26 GMT

Hi Ivan,

Try this code:

Map_S1-OPEN:

Mapping LOAD

Db&'_'&Id

,Area

FROM

[..\02_QVD\S1-OPEN.qvd](qvd);

Map_S1-Closed:

Mapping LOAD

Db&'_'&Id

,Area

FROM

[..\02_QVD\S1-Closed.qvd](qvd);

Visit:

LOAD

Db&'_'&Id as [%ID PK]

,Visitkey

,Customer

,Applymap('Map_S1-Closed',Db&'_'&Id) as AreaClosed

,Applymap('Map_S1-OPEN',Db&'_'&Id) as AreaOPEN

FROM

[..\02_QVD\S1-VISIT.qvd]

(qvd);