Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
];
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;
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;