Qlik Community

Qlik Sense App Development

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

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
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

Re: Create Data for Null/Non Existing Values

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 "

View solution in original post

Highlighted
Partner
Partner

Re: Create Data for Null/Non Existing Values

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

Re: Create Data for Null/Non Existing Values

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 "

View solution in original post

Highlighted
Partner
Partner

Re: Create Data for Null/Non Existing Values

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

Re: Create Data for Null/Non Existing Values

Thank you both!