Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kmmqlick
Contributor III
Contributor III

Scripting Issue

Hi All,

i am having sellin and sellout with different dates.Issue show in below image 

issue.PNG

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

Labels (1)
2 Replies
Vegar
MVP
MVP

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;

 

RsQK
Creator II
Creator II

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;