Qlik Community

Qlik Sense App Development

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

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Create Data for Null/Non Existing Values

I have a dataset that I joined to a master calendar where I need to fill in the missing values. Here is an example of the data:

DateLUPlan IDItem IDQty
1/1/2020    
1/2/2020FCST13334
1/3/2020    
1/1/2020SO53455
1/2/2020    
1/3/2020    
1/1/2020WO14451
1/2/2020WO14457
1/3/2020    

 

This is what I'd like to do with the dataset. Fill in the null dimension values and place a zero in for the measure.

DateLUPlan IDItem IDQty
1/1/2020FCST13330
1/2/2020FCST13334
1/3/2020FCST13330
1/1/2020SO53455
1/2/2020SO53450
1/3/2020SO53450
1/1/2020WO14451
1/2/2020WO14457
1/3/2020WO14450

 

Is there an easy way to do this?

Thanks in advance!

2 Solutions

Accepted Solutions
Highlighted
Master
Master

May be this :

Data:

LOAD * INLINE [
    Date, LU, Plan ID, Item ID, Qty
    1/1/2020,  ,  ,  ,  
    1/2/2020, FCST, 1, 333, 4
    1/3/2020,  ,  ,  ,  
    1/1/2020, SO, 5, 345, 5
    1/2/2020,  ,  ,  ,  
    1/3/2020,  ,  ,  ,  
    1/1/2020, WO, 1, 445, 1
    1/2/2020, WO, 1, 445, 7
    1/3/2020,  ,  ,  ,  
];

Tmp1:
noconcatenate
load LU, [Plan ID], [Item ID] resident Data where len(LU);

join 

load distinct Date resident Data;

Tmp2:
noconcatenate
load distinct * resident Data where len(LU);
join load * resident Tmp1;
drop table Data,Tmp1;


output:
noconcatenate

load Date, LU, [Plan ID], [Item ID], if(len(Qty)=0,0,Qty) as Qty resident Tmp2;

drop table Tmp2;

 

Capture.JPG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

Highlighted
Partner
Partner

I am assuming you have a separate table for Data and Calendar, which is the most efficient way. If not modify Step_Two to load from Data.

Data:
Load * InLine [
LU, Date, Plan ID, Item ID, Qty
FCST, 02/01/2020, 1, 333, 4
SO, 01/01/2020, 5, 345, 5
WO, 01/01/2020, 1, 445, 1
WO, 02/01/2020, 1, 445, 7
];

Calendar:
Load * InLine [
Date
01/01/2020
02/01/2020
03/01/2020
];

Step_One:
Load Distinct
Date & '-' & LU as BlankKey
Resident Data
;

Step_Two:
Load Distinct
Date as TempDate
Resident Calendar
;

Outer Join (Step_Two)
Load
LU as TempLU,
[Plan ID] as TempPlanID,
[Item ID] as TempItemID
Resident Data
;

Concatenate (Data)
Load
TempLU as LU,
TempDate as Date,
TempPlanID as [Plan ID],
TempItemID as [Item ID],
0 as Qty
Resident Step_Two
Where Not Exists(BlankKey, TempDate & '-' & TempLU)
;

Drop Tables Step_One, Step_Two;

View solution in original post

3 Replies
Highlighted
Master
Master

May be this :

Data:

LOAD * INLINE [
    Date, LU, Plan ID, Item ID, Qty
    1/1/2020,  ,  ,  ,  
    1/2/2020, FCST, 1, 333, 4
    1/3/2020,  ,  ,  ,  
    1/1/2020, SO, 5, 345, 5
    1/2/2020,  ,  ,  ,  
    1/3/2020,  ,  ,  ,  
    1/1/2020, WO, 1, 445, 1
    1/2/2020, WO, 1, 445, 7
    1/3/2020,  ,  ,  ,  
];

Tmp1:
noconcatenate
load LU, [Plan ID], [Item ID] resident Data where len(LU);

join 

load distinct Date resident Data;

Tmp2:
noconcatenate
load distinct * resident Data where len(LU);
join load * resident Tmp1;
drop table Data,Tmp1;


output:
noconcatenate

load Date, LU, [Plan ID], [Item ID], if(len(Qty)=0,0,Qty) as Qty resident Tmp2;

drop table Tmp2;

 

Capture.JPG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

Highlighted
Partner
Partner

I am assuming you have a separate table for Data and Calendar, which is the most efficient way. If not modify Step_Two to load from Data.

Data:
Load * InLine [
LU, Date, Plan ID, Item ID, Qty
FCST, 02/01/2020, 1, 333, 4
SO, 01/01/2020, 5, 345, 5
WO, 01/01/2020, 1, 445, 1
WO, 02/01/2020, 1, 445, 7
];

Calendar:
Load * InLine [
Date
01/01/2020
02/01/2020
03/01/2020
];

Step_One:
Load Distinct
Date & '-' & LU as BlankKey
Resident Data
;

Step_Two:
Load Distinct
Date as TempDate
Resident Calendar
;

Outer Join (Step_Two)
Load
LU as TempLU,
[Plan ID] as TempPlanID,
[Item ID] as TempItemID
Resident Data
;

Concatenate (Data)
Load
TempLU as LU,
TempDate as Date,
TempPlanID as [Plan ID],
TempItemID as [Item ID],
0 as Qty
Resident Step_Two
Where Not Exists(BlankKey, TempDate & '-' & TempLU)
;

Drop Tables Step_One, Step_Two;

View solution in original post

Highlighted
Partner
Partner

Thank you both!