Call null field with zero

[Thiago_Adriano]

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
];

[jmmolero · Accepted solution]

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;