issue with Left Join

[#1]

hello Gurus, I have an issue with my code, hope someone can help me

I am trying to join 2 tables USAInvSum and USABillSum using a left join with the coding below but when I see the ouptut qvd i.e.USABillInv.qvd, the records are joined based on the common fields between the 2 tables but all KPIs in the 2nd table(USABillSum) show no data and only values for the KPIs from the 1st table show up(USAInvSum), what am I doing wrong here?

USAInvSum:

Load [Material],

     [Plant],

     [Week],

     [SalesOrg],

    SUM([ValStockVal]) as [ValStockVal],

    SUM([ValStockQty]) as [ValStockQty]    

Resident USAInv

Group By [Week], [Material], [Plant], [SalesOrg];

Left Join(USAInvSum)

LOAD [Week],

     [Material],

     [Plant],

     [SalesOrg],

     SUM([COGS_DOM]) as [COGS_DOM],

     SUM([COGS_FOB]) as [COGS_FOB],

     SUM([INVC_QTY]) as [INVC_QTY],

     SUM([NetSalesAmt_DOM]) as [NetSalesAmt_DOM],

     SUM([NetSalesAmt_FOB]) as [NetSalesAmt_FOB],

     SUM([ReturnQty]) as [ReturnQty],

     SUM([ReturnAmt]) as [ReturnAmt],

     SUM([GrossQty]) as [GrossQty],

     SUM([GrossAmt]) as [GrossAmt]

Resident USABillSum

Group By Week, Material, Plant, SalesOrg;

store USAInvSum into $(vQVDPathinv)USABillInv.qvd;

[#2]

please take a look at the article in the link below - it does a good job of explaining joins in qlikview and has examples of the results using the same tables but utilizing different joins

Understanding Join, Keep and Concatenate

[#3]

a left join will only add the data from the second table that matches the first table.  If the second table is really another table with data that needs to be added to the first table regardless of what may or may not match up, you would want to do a full join

since the 2 tables do not have all fo the same columns I do not think concatenate would give you what you are looking for

[#4]

PC - any new information or update

[#5 · Accepted solution]

Hi PC's ! Try this:

USAInvSum:

Load

     [Material]&[Plant]&[Week]&[SalesOrg]      as %KEY

     [Material],

     [Plant],

     [Week],

     [SalesOrg],

    SUM([ValStockVal]) as [ValStockVal],

    SUM([ValStockQty]) as [ValStockQty]   

Resident USAInv

Group By [Week], [Material], [Plant], [SalesOrg];

Left Join

LOAD

     [Material]&[Plant]&[Week]&[SalesOrg]      as %KEY

     SUM([COGS_DOM]) as [COGS_DOM],

     SUM([COGS_FOB]) as [COGS_FOB],

     SUM([INVC_QTY]) as [INVC_QTY],

     SUM([NetSalesAmt_DOM]) as [NetSalesAmt_DOM],

     SUM([NetSalesAmt_FOB]) as [NetSalesAmt_FOB],

     SUM([ReturnQty]) as [ReturnQty],

     SUM([ReturnAmt]) as [ReturnAmt],

     SUM([GrossQty]) as [GrossQty],

     SUM([GrossAmt]) as [GrossAmt]

Resident USABillSum

Group By Week, Material, Plant, SalesOrg;

store USAInvSum into $(vQVDPathinv)USABillInv.qvd;

I hope that it help you ! Answer me for any question

Regards, Agustin

[#6]

Hey Adam, thanks for the reply and all the great info.

I went through your link above and its the Left Join I need in my case because I want to join based on whats common between the 2 tables based on the Key(Material, Plant, Week and Sales Org) and not everything that's there in the 2 tables . You are right that Concatenate wont work in my case.

My issue is that the Left Join isn't working as expected, i.e after the Join my result qvd has all the fields from the 2 tables but only shows data for fields in Table 1 and - for all Fields coming from Table 2.

[#7]

hey Agustin, thanks so much for the solution.

I did try this and looks like its working for me.

Swaroop

[maxgro]

remove the join Left Join(USAInvSum)

reload

open the table viewer to check if the tables are associated with the 4 common fields (you'll find a syn key too)

add some user interface objects to check, ie a tablebox with all fields to see where the association works and where it doesn't

maybe you have a different format for the fields? weeks?

[#9]

Thanks Maxgro, I tried the above and found the error in one of the tables where U was using the wrong field for Key.

[#10]

Great, I am glad for it!

Regards!!