topic Re: issue with Left Join in QlikView

issue with Left Join

Fri, 18 Dec 2015 16:42:47 GMT

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;

Re: issue with Left Join

Fri, 18 Dec 2015 17:31:55 GMT

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

Re: issue with Left Join

Fri, 18 Dec 2015 17:34:37 GMT

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

Re: issue with Left Join

Fri, 18 Dec 2015 19:43:37 GMT

PC - any new information or update

Re: issue with Left Join

Fri, 18 Dec 2015 19:56:49 GMT

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

Re: issue with Left Join

Mon, 21 Dec 2015 11:47:43 GMT

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.

Re: issue with Left Join

Mon, 21 Dec 2015 11:54:44 GMT

hey Agustin, thanks so much for the solution.

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

Swaroop

Re: issue with Left Join

maxgro — Mon, 21 Dec 2015 12:25:59 GMT

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?

Re: issue with Left Join

Mon, 21 Dec 2015 13:33:31 GMT

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

Re: issue with Left Join

Mon, 21 Dec 2015 19:07:54 GMT

Great, I am glad for it!

Regards!!