topic Re: Data Accumulation in script in App Development

Data Accumulation in script

Subbug_31 — Thu, 10 Sep 2020 08:35:27 GMT

Hi,

I have a data like below from source

1	26-Aug
2	26-Aug
3	26-Aug
4	27-Aug
5	27-Aug
6	27-Aug
7	27-Aug
8	27-Aug
9	28-Aug
10	28-Aug

I would like to accumulate the data and create like below in Qlik script.

26-Aug	1
26-Aug	2
26-Aug	3
27-Aug	1
27-Aug	2
27-Aug	3
27-Aug	4
27-Aug	5
27-Aug	6
27-Aug	7
27-Aug	8
28-Aug	1
28-Aug	2
28-Aug	3
28-Aug	4
28-Aug	5
28-Aug	6
28-Aug	7
28-Aug	8
28-Aug	9
28-Aug	10

Can you please help me out.

Regards

Subbu

Re: Data Accumulation in script

Taoufiq_Zarra — Thu, 10 Sep 2020 09:13:59 GMT

@Subbug_31

One solutio:

Input: load * inline [ ID, Date 1, 26-Aug 2, 26-Aug 3, 26-Aug 4, 27-Aug 5, 27-Aug 6, 27-Aug 7, 27-Aug 8, 27-Aug 9, 28-Aug 10, 28-Aug ]; left join load Date,Max(ID) as MaxTmp resident Input group by Date; Tmp: load distinct MaxTmp as Mtmp,Date as Datetmp resident Input; Final: LOAD 1 as Tmp autogenerate 1; LET NumRows=NoOfRows('Tmp'); FOR i=0 to $(NumRows)-1 Join(Final) LET varMax=Peek('Mtmp',$(i),'Tmp'); LET vdate=peek('Datetmp',$(i),'Tmp'); Let varMin = 1; LOAD $(varMin) + Iterno()-1 As IDtmp,'$(vdate)' as Date AutoGenerate 1 While $(varMin) + IterNo() -1 <= $(varMax); NEXT i drop tables Tmp,Input; DROP Field Tmp;

output:

Re: Data Accumulation in script

Kushal_Chawda — Thu, 10 Sep 2020 10:49:51 GMT

@Subbug_31 try below just to avoid complex for loops and keep script cleaner

Data: load * inline [ ID, Date 1, 26-Aug 2, 26-Aug 3, 26-Aug 4, 27-Aug 5, 27-Aug 6, 27-Aug 7, 27-Aug 8, 27-Aug 9, 28-Aug 10, 28-Aug ]; Left Join(Data) LOAD Date,max(ID) as To_Count, 1 as From_Count Resident Data Group by Date; Final: LOAD Distinct Date, From_Count+IterNo()-1 as Counter Resident Data while From_Count+IterNo()-1<=To_Count; DROP Table Data;