Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
mikegauntlett
New Contributor II

Left join not working

Hi Guys, 

 

The script below adds additional 'Of_POM_Number' to the OF table as opposed to only taking the 'Of_POM_Number' s from the the OF table. 

This means that I am loading additional data where it should be eliminated. 

Any help would be greatly appreciated Thanks in advance. 

AARO_Revenue_Date:
load
RecNo() as AARO_Revenue_Date_TABLE_RECNO,
POM_Project_Number as OF_POM_Number,
if(Min(if(AARO_Value > 0, AARO_Period, 99999)) = 99999, now() , Date(Date#(Min(if(AARO_Value > 0, AARO_Period, 99999)), 'yyMM'),'yyyy-MM-dd')) As AARO_Revenue_Period
resident All_AARO_Years where AARO_Account = '3010'
group by POM_Project_Number;

LEFT JOIN (OF)
LOAD OF_POM_Number,
AARO_Revenue_Period AS AARO_Revenue_2
Resident AARO_Revenue_Date;

AARO_Booking_Date:
load
RecNo() as AARO_Booking_Date_TABLE_RECNO,
POM_Project_Number as OF_POM_Number,
if(Min(if(AARO_Value > 0, AARO_Period, 99999)) = 99999, now() , Date(Date#(Min(if(AARO_Value > 0, AARO_Period, 99999)), 'yyMM'),'yyyy-MM-dd')) As AARO_Booking_Period
resident All_AARO_Years where AARO_Account = 'BOOKINGS'
group by POM_Project_Number;

LEFT JOIN (OF)
LOAD OF_POM_Number,
date(AARO_Booking_Period,'dd-MMMM-yyyy')AS AARO_BOOKING_2
Resident AARO_Booking_Date;

 

Labels (1)
2 Replies
vikasmahajan
Esteemed Contributor

Re: Left join not working

AARO_Revenue_Date:
load
POM_Project_Number as OF_POM_Number,
RecNo() as AARO_Revenue_Date_TABLE_RECNO,
if(Min(if(AARO_Value > 0, AARO_Period, 99999)) = 99999, now() , Date(Date#(Min(if(AARO_Value > 0, AARO_Period, 99999)), 'yyMM'),'yyyy-MM-dd')) As AARO_Revenue_Period
resident All_AARO_Years where AARO_Account = '3010'
group by POM_Project_Number;

LEFT JOIN (AARO_Revenue_Date)
LOAD OF_POM_Number,
AARO_Revenue_Period AS AARO_Revenue_2
Resident AARO_Revenue_Date;

AARO_Booking_Date:
load
POM_Project_Number as OF_POM_Number,
RecNo() as AARO_Booking_Date_TABLE_RECNO,
if(Min(if(AARO_Value > 0, AARO_Period, 99999)) = 99999, now() , Date(Date#(Min(if(AARO_Value > 0, AARO_Period, 99999)), 'yyMM'),'yyyy-MM-dd')) As AARO_Booking_Period
resident All_AARO_Years where AARO_Account = 'BOOKINGS'
group by POM_Project_Number;

LEFT JOIN (AARO_Booking_Date)
LOAD OF_POM_Number,
date(AARO_Booking_Period,'dd-MMMM-yyyy')AS AARO_BOOKING_2
Resident AARO_Booking_Date;

mikegauntlett
New Contributor II

Re: Left join not working

Unfortunately not, 

when the OF table is loaded on its own, it works. 

Once the aaro tables are loaded they are adding additional project number. Which should not be loaded.