How to combine 2 values in different, non-successive rows in one row?

[boudewijnleunis]

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

[Taoufiq_Zarra]

can you share a sample data in Excel or other format

and can you give an example from this sample ?

[boudewijnleunis]

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

[Taoufiq_Zarra]

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 ?

[boudewijnleunis]

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?

[jmmolero]

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;

[boudewijnleunis]

@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.  

[Saravanan_Desingh · Accepted solution]

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;