# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
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;