Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

srikanthsri1
Valued Contributor

Left join issue

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Left join issue

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.

9 Replies
srikanthsri1
Valued Contributor

Re: Left join issue

if needed i can attach the the data file  too

Any suggestion please

dmohanty
Valued Contributor

Re: Left join issue

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.

MVP
MVP

Re: Left join issue

Hi Srikanth,

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

Regards,

Jagan.

Gabriel
Valued Contributor II

Re: Left join issue

Hi,

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

Regards,

Gabriel

srikanthsri1
Valued Contributor

Re: Left join issue

plz find the attached qvd

srikanthsri1
Valued Contributor

Re: Left join issue

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

srikanthsri1
Valued Contributor

Re: Left join issue

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

MVP
MVP

Re: Left join issue

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.

srikanthsri1
Valued Contributor

Re: Left join issue

Thank you Very Much Jagan,

You are right .. it's working now !

Community Browser