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