Join Tables with Out Loops

[#1]

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.

[swuehl]

I think your image is not showing all information needed.

Please have a look into

Circular References

[Peter_Cammaert]

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

[#4]

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.

[jagan]

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.

[Peter_Cammaert]

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.

[#7]

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.

[#8]

Missing attachment