topic Re: Left join issue in QlikView

Left join issue

Anonymous — Wed, 13 Feb 2013 14:16:14 GMT

i'm trying to do a left join to bring one set of values to my main table. but it show null for the added coulmn

but when i remove join and load it as a seperate values it giving us correct values ..

how to fix this issue ? this the script;

table1:
Load
Product&'-'&[Package Type]&'-'&kWeekKey as kOOChemKey,
kWeekKey,
[Customer Requested Delivery Date],
[Business Segment],
Product,
[Order Status],
[Package Type],
sum(GALLON) as CFENRL

Resident tablex
Group by [Customer Requested Delivery Date],kWeekKey,[Order Status],[Business Segment], Product, [Package Type];

left join

Load
Product&'-'&[Package Type]&'-'&kWeekKey as kOOChemKey,
kWeekKey,
[Customer Requested Delivery Date],
[Business Segment],
Product,
[Order Status],
[Package Type],
sum(GALLON) as CFEN

Resident tablex
Where [Order Status]<>'RL'
Group by [Customer Requested Delivery Date],kWeekKey,[Order Status],[Business Segment], Product, [Package Type];

values for CFEN are all null for my main table1

Any suggestion plz

Re: Left join issue

Anonymous — Wed, 13 Feb 2013 14:45:25 GMT

if needed i can attach the the data file too

Any suggestion please

Re: Left join issue

dmohanty — Wed, 13 Feb 2013 14:47:47 GMT

Hi,

I am unable to open your app, but I assume that your Order Status column has the only value 'RL' and when your are ignoring that in 2nd script, CFEN is not finding any values associated with that.

Your NULL values are probably because of WHERE condition in 2nd script. Please comment and try to run it, then you may understand.

Thanks.

Re: Left join issue

jagan — Wed, 13 Feb 2013 14:49:44 GMT

Hi Srikanth,

Can you attach sample data from both the tables, It helps in understanding the issue.

Regards,

Jagan.

Re: Left join issue

Gabriel — Wed, 13 Feb 2013 14:52:57 GMT

Hi,

Why not use MAPPING.....Apply() function.

Regards,

Gabriel

Re: Left join issue

Anonymous — Wed, 13 Feb 2013 14:54:52 GMT

plz find the attached qvd

Re: Left join issue

Anonymous — Wed, 13 Feb 2013 14:56:28 GMT

i'm using resident and doing a where condition to my second table and doind a left join

Re: Left join issue

Anonymous — Wed, 13 Feb 2013 15:05:35 GMT

i just commented out the the where condition and didn't worked

but the orderstatus has 3 values of those one is elimentaed using where condition

Re: Left join issue

jagan — Wed, 13 Feb 2013 15:07:26 GMT

Hi,

Do you need to join the tables based on all the fields, I think if you use all the fields while joining there is no record matches. I just joined the table by using the key field and I am getting the values, Please try below script.

left join (ChemOOTestSum)

Load

Product&'-'&[Package Type]&'-'&kWeekKey as kOOChemKey,

//if([Order Status]<>'RL',Sum(GALLON)) as CFEN,

sum(GALLON) as CFEN

Resident ChemOpenOrderTemp

Where [Order Status]<>'RL'

Group by [Customer Requested Delivery Date],kWeekKey,[Order Status],[Business Segment], Product, [Package Type];

Regards,

Jagan.

Re: Left join issue

Anonymous — Wed, 13 Feb 2013 15:21:36 GMT

Thank you Very Much Jagan,

You are right .. it's working now !