topic Re: Scripting Issue in App Development

Scripting Issue

kmmqlick — Tue, 30 Nov 2021 16:25:32 GMT

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

Re: Scripting Issue

Vegar — Tue, 02 Nov 2021 06:42:01 GMT

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;

Re: Scripting Issue

RsQK — Tue, 02 Nov 2021 11:52:24 GMT

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;