Discussion Board for collaboration on QlikView Scripting.
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.
LOAD * INLINE [DATE, PRODUCT, COMPONENT, QTY07/27 / 2020.58455,5845501.5007/27/2020,58455,5845502,6007/28 / 2020,58455,5845501,10007/28 / 2020,58455,5845502,10007/28 / 2020,58455,5845503,55];
LOAD * INLINE [PRODUCT, COMPONENT58455,584550158455,584550258455,5845503];
Fact:LOAD * INLINE [DATE, PRODUCT, COMPONENT, QTY07/27/2020,58455,5845501,5007/27/2020,58455,5845502,6007/28/2020,58455,5845501,10007/28/2020,58455,5845502,10007/28/2020,58455,5845503,55];
Register:LOAD * INLINE [PRODUCT, COMPONENT58455,584550158455,584550258455,5845503];
FOR Each vDate in FieldValueList('DATE')Fact_tmp:load Distinct COMPONENTResident Register;Join(Fact_tmp)LOAD *Resident Factwhere DATE = '$(vDate)';Fact_final:LOAD *,if(IsNull(DATE), Date#('$(vDate)','DD/MM/YYYY'), DATE) as NEW_DATE,if(IsNull(DATE), 0, QTY) as NEW_QTYResident Fact_tmp;Drop Table Fact_tmp;
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