Left join issue

[Anonymous]

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

[Anonymous]

if needed i can attach the the data file  too

Any suggestion please

[dmohanty]

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.

[jagan]

Hi Srikanth,

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

Regards,

Jagan.

[Gabriel]

Hi,

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

Regards,

Gabriel

[Anonymous]

plz find the attached qvd

[Anonymous]

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

[Anonymous]

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

[jagan · Accepted solution]

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.

[Anonymous]

Thank you Very Much Jagan,

You are right .. it's working now !