Two left joins only first left join bringing back results

[ivandrago]

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

[vishsaggi]

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];

[marcginqo]

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

[ivandrago]

Yes I do have data in there.

Unfortunately the above has not worked?

[venkatraju]

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);