topic Re: Join Tables with Out Loops in QlikView

Join Tables with Out Loops

Mon, 29 Jun 2015 14:35:18 GMT

Hi,

I have problem joining two tables, in the below model i have OrderDetail, PeriodDimension and TruckActive. OrderDetail and TruckActive has TruckNo, and TruckActive has TruckType. If i join OrderDetail and TruckActive on TruckNo to get TruckType, it's creating a loop. i have to filter data on Trucktype for both OrderDetail and TruckActive.

Re: Join Tables with Out Loops

swuehl — Mon, 29 Jun 2015 14:40:41 GMT

I think your image is not showing all information needed.

Please have a look into

Circular References

Re: Join Tables with Out Loops

Peter_Cammaert — Mon, 29 Jun 2015 14:42:53 GMT

If you really want to JOIN columns, then add this to your script:

LEFT JOIN (OrderDetail)

LOAD ActiveTruckNo AS TruckNo,

ActiveTruckType AS TruckType

RESIDENT TruckActive;

You may have to add a Date as a JOIN match field because it seems that the TruckActive table contains entries for trucks that are active on dates/in periods.However, I cannot see which Order field contains the Order Date.

Peter

Re: Join Tables with Out Loops

Mon, 29 Jun 2015 14:52:05 GMT

Hi Peter,

As you can see from the Model, OrderDetail Is Joined to OrderHeader on ProNumber and OrderHeader is Joined to Consolidated and Consolidated is Joined to PeriodDimesion. My requirement is to filter data from both TruckActive and OrderDetail based on TruckType.

Re: Join Tables with Out Loops

jagan — Mon, 29 Jun 2015 14:54:17 GMT

HI,

If you want to join the tables then you have to use Left Join/Right Join/Inner Join based on the requirement.

Example:

Table1:

LOAD

FROM Table1;

Left Join(Table2)

LOAD

FROM Table2;

Hope this helps you.

Regards,

Jagan.

Re: Join Tables with Out Loops

Peter_Cammaert — Mon, 29 Jun 2015 15:05:51 GMT

Linking internal tables is not the same as a JOIN, although the effect could be the same. JOINs happen in your Load Script. That's why everyone is suggesting script solutions.

I think the data model you're after is largely there already. The only problem is that your OrderHeaders are linked to an aggregation table and not to your Period table (or a link table). It is unclear what field you are using to connect OrderHeaders to consolidated data. Can you connect orderheaders straight to the PeriodDimension table and turn it into a link table by adding required dimension values to the different key fields?

This is going to be a bit of a hit/miss story if we cannot get a (optionally reduced) example document to work on. Can you upload oyur qvw in some form or another?

Peter.

Re: Join Tables with Out Loops

Tue, 30 Jun 2015 14:17:08 GMT

Hi Peter,

I have attached my document, Can you please take a look! i'm new to Qlikview and tried my best with the data model.

Re: Join Tables with Out Loops

Tue, 30 Jun 2015 14:38:48 GMT

Missing attachment