# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for
Did you mean:
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:

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:

PRODUCT, COMPONENT
58455,5845501
58455,5845502
58455,5845503
];

1 Solution

Accepted Solutions
Partner

Hi,

check this:

Fact:
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:
PRODUCT, COMPONENT
58455,5845501
58455,5845502
58455,5845503
];

FOR Each vDate in FieldValueList('DATE')

Fact_tmp:
Resident Register;

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

Fact_final:
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;

Partner

Hi,

check this:

Fact:
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:
PRODUCT, COMPONENT
58455,5845501
58455,5845502
58455,5845503
];

FOR Each vDate in FieldValueList('DATE')

Fact_tmp:
Resident Register;

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

Fact_final:
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;