Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Thiago_Adriano
Contributor II
Contributor II

Call null field with zero

Mr. My question is very simple to solve in Excel, but in Qlik I can't get a solution, I hope to solve it here.

I am trying to place the "COMPONENT" that is not listed on that day within the Fact table, bringing it with "QTY" = 0.

That in the example below, it would be COMPONENT = 5845503 on DATE = 27/07/2020 because it is the only one missing.

Fact:

LOAD * INLINE [
DATE, PRODUCT, COMPONENT, QTY
07/27 / 2020.58455,5845501.50
07/27/2020,58455,5845502,60
07/28 / 2020,58455,5845501,100
07/28 / 2020,58455,5845502,100
07/28 / 2020,58455,5845503,55
];

Register:

LOAD * INLINE [
PRODUCT, COMPONENT
58455,5845501
58455,5845502
58455,5845503
];

1 Solution

Accepted Solutions
jmmolero
Partner
Partner

Hi,

check this:

Fact:
LOAD * INLINE [
DATE, PRODUCT, COMPONENT, QTY
07/27/2020,58455,5845501,50
07/27/2020,58455,5845502,60
07/28/2020,58455,5845501,100
07/28/2020,58455,5845502,100
07/28/2020,58455,5845503,55
];


Register:
LOAD * INLINE [
PRODUCT, COMPONENT
58455,5845501
58455,5845502
58455,5845503
];

FOR Each vDate in FieldValueList('DATE')

Fact_tmp:
load Distinct COMPONENT
Resident Register;

Join(Fact_tmp)
LOAD *
Resident Fact
where DATE = '$(vDate)';


Fact_final:
LOAD *,
if(IsNull(DATE), Date#('$(vDate)','DD/MM/YYYY'), DATE) as NEW_DATE,
if(IsNull(DATE), 0, QTY) as NEW_QTY
Resident Fact_tmp;

Drop Table Fact_tmp;

 

next vDate

Drop Table Fact;

RENAME Table Fact_final to Fact;
DROP Fields DATE,QTY from Fact;

RENAME Field NEW_DATE to DATE;
RENAME Field NEW_QTY to QTY;

View solution in original post

1 Reply
jmmolero
Partner
Partner

Hi,

check this:

Fact:
LOAD * INLINE [
DATE, PRODUCT, COMPONENT, QTY
07/27/2020,58455,5845501,50
07/27/2020,58455,5845502,60
07/28/2020,58455,5845501,100
07/28/2020,58455,5845502,100
07/28/2020,58455,5845503,55
];


Register:
LOAD * INLINE [
PRODUCT, COMPONENT
58455,5845501
58455,5845502
58455,5845503
];

FOR Each vDate in FieldValueList('DATE')

Fact_tmp:
load Distinct COMPONENT
Resident Register;

Join(Fact_tmp)
LOAD *
Resident Fact
where DATE = '$(vDate)';


Fact_final:
LOAD *,
if(IsNull(DATE), Date#('$(vDate)','DD/MM/YYYY'), DATE) as NEW_DATE,
if(IsNull(DATE), 0, QTY) as NEW_QTY
Resident Fact_tmp;

Drop Table Fact_tmp;

 

next vDate

Drop Table Fact;

RENAME Table Fact_final to Fact;
DROP Fields DATE,QTY from Fact;

RENAME Field NEW_DATE to DATE;
RENAME Field NEW_QTY to QTY;

View solution in original post