Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
See dataset below.
Shown are periods of illness of a single student over time. Problem is that in the database for every 1 day of illness there's created a new row. However in my resulttable I want to have just 1 record with the first day of illness as StartDate and last day of illness as EndDate. It should then look like this:
Does anyone can help me with the proper Qlikview script to get this done?
Regards,
Boudewijn
Please check this. I have pulled only few data for my development. Please change the input as required.
tab1:
LOAD ID,
Date(Date#(StartDate,'YYYY-MM-DD')) As StartDate,
Date(Floor(Date#(EndDate,'YYYY-MM-DD hh:mm:ss'))) As EndDate,
Diff_Start_End_SameRow,
Diff_Start_StartPrevRow
FROM
[C:\Users\sarav\Downloads\Example1.xlsx]
(ooxml, embedded labels, table is Sheet1)
Where ID=400048 And Year(StartDate)=2020;
tab2:
LOAD *, Date(StartDate - Cnt_Date + 1) As StartDate_Min;
LOAD *, If(IsNull(NewStartDate),1,RangeSum(Peek(Cnt_Date),1)) As Cnt_Date;
LOAD *, If(Previous(StartDate)=Date(StartDate-1),Peek(StartDate)) As NewStartDate
Resident tab1
Order By ID, StartDate
;
Left Join(tab2)
LOAD ID, StartDate_Min, Date(StartDate_Min + Max(Cnt_Date) - 1) As StartDate_Max
, Max(Cnt_Date) As [No Of Days Illness]
Resident tab2
Group By ID, StartDate_Min
;
Drop Table tab1;
can you share a sample data in Excel or other format
and can you give an example from this sample ?
Hi Taoufiq,
See sample attached. Is an extract of database itselves. What exactly do you mean by giving an example from this sample? It's already in my original post, not?
Regards,
Boudewijn
for examplel :
717 | 2019-11-20 | 2019-11-20 23:59:59 | 1 | 268 |
717 | 2019-02-25 08:30:00 | 2019-03-10 23:59:00 | 14 | -211 |
717 | 2020-02-12 07:39:00 | 2020-02-12 23:00:00 | 1 | 84 |
what is the result output ?
can you explain 14 ? -211?268 ...
line 2 start 25/02 and end date 10/03 why is not a same day ?
14 = number of days between StartDate of illness period (in this case: February 25th, 2019) and EndDate of illness period (in this case the last day of illness is March 10th, 2019)
-211 = number of days between the StartDate in its own row/record compared to the StartDate in de previous row Record , in this case February 25th, 2019 compared with November 20th, 2019. Was a column I already created in my query on database but can be ignored.
line 2 (not same day) is because in same table in database cases of both my primer question (so for each day of illness a new record) as well as were StartDate and EndDate are already in same row/record as I would like to have it in my resulttable are possible.
Difficult one, not?
Hi,
Try this:
[Sheet1]:
LOAD
[ID],
Timestamp(Timestamp#([StartDate], 'YYYY-MM-DD hh:mm:ss') ) AS [StartDate],
Timestamp(Timestamp#([EndDate], 'YYYY-MM-DD hh:mm:ss') ) AS [EndDate],
[Diff_Start_End_SameRow],
[Diff_Start_StartPrevRow]
FROM [lib://AttachedFiles/Example1.xlsx]
(ooxml, embedded labels, table is Sheet1);
Load ID,
FirstValue(StartDate),
LastValue(EndDate),
sum(Diff_Start_End_SameRow)
Resident Sheet1
Group by ID
Order by StartDate asc;
Drop Table Sheet1;
@jmmolero : Unfortunately that doesn't work. I.e. ID 717: result shows no FirstValue(StartDate) and as LastValue(EndDate) it takes Max of all EndDates and that's not wanted. With ID 717 records are correctly shown in source table. Better take a look at ID 400048 as an example.
Please check this. I have pulled only few data for my development. Please change the input as required.
tab1:
LOAD ID,
Date(Date#(StartDate,'YYYY-MM-DD')) As StartDate,
Date(Floor(Date#(EndDate,'YYYY-MM-DD hh:mm:ss'))) As EndDate,
Diff_Start_End_SameRow,
Diff_Start_StartPrevRow
FROM
[C:\Users\sarav\Downloads\Example1.xlsx]
(ooxml, embedded labels, table is Sheet1)
Where ID=400048 And Year(StartDate)=2020;
tab2:
LOAD *, Date(StartDate - Cnt_Date + 1) As StartDate_Min;
LOAD *, If(IsNull(NewStartDate),1,RangeSum(Peek(Cnt_Date),1)) As Cnt_Date;
LOAD *, If(Previous(StartDate)=Date(StartDate-1),Peek(StartDate)) As NewStartDate
Resident tab1
Order By ID, StartDate
;
Left Join(tab2)
LOAD ID, StartDate_Min, Date(StartDate_Min + Max(Cnt_Date) - 1) As StartDate_Max
, Max(Cnt_Date) As [No Of Days Illness]
Resident tab2
Group By ID, StartDate_Min
;
Drop Table tab1;