Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello 😊
I'm starting to make something a bit easy with another language but with qlik I don't find how.
With this table by ID :
ID | date start | date end | end - start |
A | 2020-01-31 | 2020-02-02 | 3 |
How can I do in script or set analysis (or both solutions to compare ^^!) something like this (to make a graphic by month cumulated) :
ID | Period BY MONTH | number of day TOTAL ID | number of day BY MONTH | number of day BY MONTH CUMULATED |
A | 2020-01 | 3 | 1 | 1 |
A | 2020-02 | 3 | 2 | 3 |
Thanks for reading.
@Max1 if you have multiple ID you can try below :
Data:
load *,[date end]-[date start]+1 as [number of day TOTAL ID];
LOAD ID, Date#([date start],'YYYY-MM-DD') as [date start], Date#([date end],'YYYY-MM-DD') as [date end] INLINE [
ID, date start, date end
A, 2020-01-31, 2020-02-02
];
left join
load ID ,Date(Date#([date start],'YYYY-MM-DD') + IterNo() - 1) as TempDate,Month(Date#([date start],'YYYY-MM-DD') + IterNo() - 1) as TmpMonth,Year(Date#([date start],'YYYY-MM-DD') + IterNo() - 1)&'-'&Num(Month(Date#([date start],'YYYY-MM-DD') + IterNo() - 1),'00') as [Period BY MONTH] resident Data
While Date#([date start],'YYYY-MM-DD') + IterNo() -1 <= Date#([date end],'YYYY-MM-DD');
left join load ID,TmpMonth,count(TempDate) as [number of day BY MONTH] resident Data group by ID,TmpMonth;
Tmp:
noconcatenate
load distinct ID,[Period BY MONTH],[number of day TOTAL ID],[number of day BY MONTH] resident Data;
drop table Data;
output:
noconcatenate
load *,if(peek(ID)=ID,peek([number of day BY MONTH] )+[number of day BY MONTH] ,1) as [number of day BY MONTH CUMULATED] resident Tmp;
drop table Tmp;
output:
to change Inline [...] by your load form database or file...
Hi, @Max1 try this:
Date:
LOAD * INLINE [
ID,Start, End
A,2020-01-31, 2020-02-02
];
Result:
Load
ID,
Start,
End,
End-Start+1 as NumberOfDayTotalID
Resident Date;
drop table Date;
Let vStart = num(peek('Start',0, 'Date2'));
Let vEnd = num(peek('End',0, 'Date2'));
Let vDays = num(peek('NumberOfDayTotalID',0, 'Date2'));
Let vID = peek('ID',0, 'Date2');
Dates:
Load
'$(vID)' as ID,
monthname(if(rowno()=1, $(vStart), peek(Date)+1)) as Period,
if(rowno()=1, $(vStart), peek(Date)+1) as Date
autogenerate($(vDays));
Month:
Load
ID,
Period,
count(Date) as NumberOfDay
Resident Dates
group By ID, Period;
drop table Dates;
LEFT join (Result)
Load
*,
if(rowno()=1, NumberOfDay, NumberOfDay + peek(NumberOfDayCumulated)) as NumberOfDayCumulated
Resident Month;
drop table Month;
//This will solve just one ID .....
@Max1 if you have multiple ID you can try below :
Data:
load *,[date end]-[date start]+1 as [number of day TOTAL ID];
LOAD ID, Date#([date start],'YYYY-MM-DD') as [date start], Date#([date end],'YYYY-MM-DD') as [date end] INLINE [
ID, date start, date end
A, 2020-01-31, 2020-02-02
];
left join
load ID ,Date(Date#([date start],'YYYY-MM-DD') + IterNo() - 1) as TempDate,Month(Date#([date start],'YYYY-MM-DD') + IterNo() - 1) as TmpMonth,Year(Date#([date start],'YYYY-MM-DD') + IterNo() - 1)&'-'&Num(Month(Date#([date start],'YYYY-MM-DD') + IterNo() - 1),'00') as [Period BY MONTH] resident Data
While Date#([date start],'YYYY-MM-DD') + IterNo() -1 <= Date#([date end],'YYYY-MM-DD');
left join load ID,TmpMonth,count(TempDate) as [number of day BY MONTH] resident Data group by ID,TmpMonth;
Tmp:
noconcatenate
load distinct ID,[Period BY MONTH],[number of day TOTAL ID],[number of day BY MONTH] resident Data;
drop table Data;
output:
noconcatenate
load *,if(peek(ID)=ID,peek([number of day BY MONTH] )+[number of day BY MONTH] ,1) as [number of day BY MONTH CUMULATED] resident Tmp;
drop table Tmp;
output:
to change Inline [...] by your load form database or file...
Hi @Taoufiq_Zarra and @QFabian
Thank you very much for your help, I have choose the 2nd solution (wich work with multiple ID) 🙂
I have just change some things like the left joins by noconcatenate and no distinct, I understand a bit more with this but I the same result.
And just add "CUMULATED" in peek 😉
load *,if(peek(ID)=ID,peek([number of day BY MONTH] )+[number of day BY MONTH CUMULATED] ,1) as [number of day BY MONTH CUMULATED] resident Tmp;
I'm trying now to ajust performance because I have a lot of line in source. Make one line by day take a long time.
Have a good day.