topic Populate missing values in a fact table in QlikView https://community.qlik.com/t5/QlikView/Populate-missing-values-in-a-fact-table/m-p/1525129#M438339 <P>hi all&nbsp;</P><P>in my model i have a fact table with orders and order status</P><P>order status field appear only once in the date&nbsp; was modified ,</P><P>i would want to populate the missing values in&nbsp;<SPAN>order status field</SPAN> with the relevant status i.e bellow&nbsp;</P><P>status=Open from 20180607 to 20180620</P><P>status=Reopen from 20180622 to 20180709</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>order_id</TD><TD>OrderDate</TD><TD>Date</TD><TD>Status</TD><TD>finalDate</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180607</TD><TD>Opened</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180608</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180609</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180610</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180611</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180612</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180613</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180614</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180615</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180616</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180617</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180618</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180619</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180620</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180621</TD><TD>On Hold</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180622</TD><TD>ReOpened</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180623</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180624</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180625</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180626</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180627</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180628</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180629</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180630</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180701</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180702</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180703</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180704</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180705</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180706</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180707</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180708</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180709</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180710</TD><TD>Closed</TD><TD>20180710</TD></TR></TBODY></TABLE><P>&nbsp;</P> Sat, 16 Nov 2024 21:40:25 GMT ilanbaruch 2024-11-16T21:40:25Z Populate missing values in a fact table https://community.qlik.com/t5/QlikView/Populate-missing-values-in-a-fact-table/m-p/1525129#M438339 <P>hi all&nbsp;</P><P>in my model i have a fact table with orders and order status</P><P>order status field appear only once in the date&nbsp; was modified ,</P><P>i would want to populate the missing values in&nbsp;<SPAN>order status field</SPAN> with the relevant status i.e bellow&nbsp;</P><P>status=Open from 20180607 to 20180620</P><P>status=Reopen from 20180622 to 20180709</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>order_id</TD><TD>OrderDate</TD><TD>Date</TD><TD>Status</TD><TD>finalDate</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180607</TD><TD>Opened</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180608</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180609</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180610</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180611</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180612</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180613</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180614</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180615</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180616</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180617</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180618</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180619</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180620</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180621</TD><TD>On Hold</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180622</TD><TD>ReOpened</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180623</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180624</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180625</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180626</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180627</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180628</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180629</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180630</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180701</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180702</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180703</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180704</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180705</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180706</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180707</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180708</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180709</TD><TD>&nbsp;</TD><TD>20180710</TD></TR><TR><TD>35878191543</TD><TD>20180607</TD><TD>20180710</TD><TD>Closed</TD><TD>20180710</TD></TR></TBODY></TABLE><P>&nbsp;</P> Sat, 16 Nov 2024 21:40:25 GMT https://community.qlik.com/t5/QlikView/Populate-missing-values-in-a-fact-table/m-p/1525129#M438339 ilanbaruch 2024-11-16T21:40:25Z Re: Populate missing values in a fact table https://community.qlik.com/t5/QlikView/Populate-missing-values-in-a-fact-table/m-p/1525130#M438340 <P>Try this</P><PRE>Table: LOAD * INLINE [ order_id, OrderDate, Date, Status, finalDate 35878191543, 20180607, 20180607, Opened, 20180710 35878191543, 20180607, 20180608, , 20180710 35878191543, 20180607, 20180609, , 20180710 35878191543, 20180607, 20180610, , 20180710 35878191543, 20180607, 20180611, , 20180710 35878191543, 20180607, 20180612, , 20180710 35878191543, 20180607, 20180613, , 20180710 35878191543, 20180607, 20180614, , 20180710 35878191543, 20180607, 20180615, , 20180710 35878191543, 20180607, 20180616, , 20180710 35878191543, 20180607, 20180617, , 20180710 35878191543, 20180607, 20180618, , 20180710 35878191543, 20180607, 20180619, , 20180710 35878191543, 20180607, 20180620, , 20180710 35878191543, 20180607, 20180621, On Hold, 20180710 35878191543, 20180607, 20180622, ReOpened, 20180710 35878191543, 20180607, 20180623, , 20180710 35878191543, 20180607, 20180624, , 20180710 35878191543, 20180607, 20180625, , 20180710 35878191543, 20180607, 20180626, , 20180710 35878191543, 20180607, 20180627, , 20180710 35878191543, 20180607, 20180628, , 20180710 35878191543, 20180607, 20180629, , 20180710 35878191543, 20180607, 20180630, , 20180710 35878191543, 20180607, 20180701, , 20180710 35878191543, 20180607, 20180702, , 20180710 35878191543, 20180607, 20180703, , 20180710 35878191543, 20180607, 20180704, , 20180710 35878191543, 20180607, 20180705, , 20180710 35878191543, 20180607, 20180706, , 20180710 35878191543, 20180607, 20180707, , 20180710 35878191543, 20180607, 20180708, , 20180710 35878191543, 20180607, 20180709, , 20180710 35878191543, 20180607, 20180710, Closed, 20180710 ]; FinalTable: NoConcatenate LOAD order_id, OrderDate, Date, If(order_id = Previous(order_id), If(Len(Trim(Status)) = 0, Peek('Status'), Status), Status) as Status, finalDate Resident Table Order By order_id, Date; DROP Table Table;</PRE> Mon, 31 Dec 2018 17:14:31 GMT https://community.qlik.com/t5/QlikView/Populate-missing-values-in-a-fact-table/m-p/1525130#M438340 sunny_talwar 2018-12-31T17:14:31Z