https://community.qlik.com/t5/App-Development/Filling-gaps-in-a-table-through-manipulation/m-p/2132271#M92025 <P>Hello all,</P> <P>I have something similar to the following table:<BR /><BR /></P> <P>Periode&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Change&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; InventoryNumber</P> <P>202205&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 05&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1E5KS</P> <P>202207&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 06&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1E5KS</P> <P>202209&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 07&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1E5KS</P> <P>202301&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2G8LM</P> <P>202304&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2G8LM</P> <P>&nbsp;</P> <P>I've been thinking about how to go about it with the Qlik View loading script to create a table that would look like this:</P> <P>&nbsp;</P> <P>Periode&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Change&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; InventoryNumber</P> <P>202205&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 05&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1E5KS</P> <P>202206&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 05&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1E5KS &nbsp; &nbsp;</P> <P>202207&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 06&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1E5KS</P> <P>202208&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 06&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1E5KS</P> <P>202209&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 07&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1E5KS</P> <P>202301&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2G8LM</P> <P>202302&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2G8LM</P> <P>202303&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2G8LM</P> <P>202304&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2G8LM</P> <P>&nbsp;</P> <P>Does anyone have an idea how to do this elegantly? until now I've been thinking about using PEEK() and AUTOGENERATE but I'm struggling.</P> <P>Thank you in advance.</P> <P>&nbsp;</P> Thu, 26 Oct 2023 19:41:17 GMT luke_brady 2023-10-26T19:41:17Z https://community.qlik.com/t5/App-Development/Filling-gaps-in-a-table-through-manipulation/m-p/2132271#M92025 <P>Hello all,</P> <P>I have something similar to the following table:<BR /><BR /></P> <P>Periode&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Change&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; InventoryNumber</P> <P>202205&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 05&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1E5KS</P> <P>202207&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 06&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1E5KS</P> <P>202209&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 07&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1E5KS</P> <P>202301&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2G8LM</P> <P>202304&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2G8LM</P> <P>&nbsp;</P> <P>I've been thinking about how to go about it with the Qlik View loading script to create a table that would look like this:</P> <P>&nbsp;</P> <P>Periode&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Change&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; InventoryNumber</P> <P>202205&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 05&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1E5KS</P> <P>202206&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 05&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1E5KS &nbsp; &nbsp;</P> <P>202207&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 06&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1E5KS</P> <P>202208&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 06&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1E5KS</P> <P>202209&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 07&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1E5KS</P> <P>202301&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2G8LM</P> <P>202302&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2G8LM</P> <P>202303&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2G8LM</P> <P>202304&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2G8LM</P> <P>&nbsp;</P> <P>Does anyone have an idea how to do this elegantly? until now I've been thinking about using PEEK() and AUTOGENERATE but I'm struggling.</P> <P>Thank you in advance.</P> <P>&nbsp;</P> Thu, 26 Oct 2023 19:41:17 GMT https://community.qlik.com/t5/App-Development/Filling-gaps-in-a-table-through-manipulation/m-p/2132271#M92025 luke_brady 2023-10-26T19:41:17Z https://community.qlik.com/t5/App-Development/Filling-gaps-in-a-table-through-manipulation/m-p/2133170#M92089 <P>Hi,</P> <P>The provided solution is elaborate, but it can be condensed based on the data model.</P> <LI-CODE lang="markup">data: load date(date#(Periode,'YYYYMM'),'YYYYMM') as Periode, Change, InventoryNumber, InventoryNumber&amp;Periode as key; load * Inline [ Periode,Change, InventoryNumber 202205,05,1E5KS 202207,06,1E5KS 202209,07,1E5KS 202301,01,2G8LM 202304,02,2G8LM]; Concatenate(data) load InventoryNumber, date(MonthName(Missing_Date),'YYYYMM') as Periode where not(Exists(key,InventoryNumber&amp;date(MonthName(Missing_Date),'YYYYMM'))); load InventoryNumber, Date([Starttime] + IterNo() - 1) as Missing_Date While ([Starttime] + IterNo() - 1) &lt;= [EndTime]; load InventoryNumber, date(Max(Periode)) as EndTime, date(Min(Periode)) as Starttime Resident data Group by InventoryNumber; FinaldataTemp: load Distinct * where not(IsNull(Change)); load Distinct Periode, if( (IsNull(Change) or Len(Trim(Change))=0) and previous(InventoryNumber) = InventoryNumber ,previous(Change),Change ) as Change, InventoryNumber; load Distinct InventoryNumber, Change, Periode, 15425155 as jjj Resident data Order by InventoryNumber , Periode ; drop Tables data; exit Script; </LI-CODE> <P><BR /><BR /></P> Tue, 31 Oct 2023 06:45:14 GMT https://community.qlik.com/t5/App-Development/Filling-gaps-in-a-table-through-manipulation/m-p/2133170#M92089 ajaykakkar93 2023-10-31T06:45:14Z