topic Left join not working in App Development

Left join not working

mikegauntlett — Sat, 16 Nov 2024 06:09:25 GMT

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;

Re: Left join not working

vikasmahajan — Thu, 04 Apr 2019 11:23:19 GMT

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;

Re: Left join not working

mikegauntlett — Thu, 04 Apr 2019 13:25:18 GMT

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.