Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
i am having sellin and sellout with different dates.Issue show in below image
some days dont have sellin and some dates dont have sellout values.But combinely i want a table with missed values as zero in both the cases
If the zeros are important then try something like this.
TableAB:
Load * tableA;
Join Load * TableB;
Table:
NoConcatenate Load
WeekKendDate,
alt(Sellin,0) AS Sellin,
alt(Sellout,0) AS Sellout
Resident tableAB;
Drop table tableAB;
Hey, try this:
temp_sellin:
LOAD * INLINE [
WeeKendDate,Sellin
02.10.2021,10
16.10.2021,200
23.10.2021,100
30.10.2021,50
06.11.2021,32
13.11.2021,45
20.11.2021,78
27.11.2021,10
];
temp_sellout:
LOAD * INLINE [
WeeKendDate,Sellout
09.10.2021,5
16.10.2021,77
23.10.2021,42
30.10.2021,12
06.11.2021,10
13.11.2021,4
27.11.2021,8
];
temp_final_table:
LOAD DISTINCT
WeeKendDate
RESIDENT temp_sellin;
CONCATENATE (temp_final_table)
LOAD DISTINCT
WeeKendDate
RESIDENT temp_sellout;
JOIN (temp_final_table)
LOAD
WeeKendDate,
SUM(Sellin) AS Sellin
RESIDENT temp_sellin
GROUP BY WeeKendDate;
DROP TABLE temp_sellin;
JOIN (temp_final_table)
LOAD
WeeKendDate,
SUM(Sellout) AS Sellout
RESIDENT temp_sellout
GROUP BY WeeKendDate;
DROP TABLE temp_sellout;
final_table:
NoConcatenate
LOAD
WeeKendDate,
RANGESUM(Sellin,0) AS Sellin,
RANGESUM(Sellout,0) AS Sellout
RESIDENT temp_final_table;
DROP TABLE temp_final_table;