topic Re: How to merge data in QlikView

How to merge data

Fri, 01 Nov 2013 03:32:16 GMT

i want to merge the data below 2 into one row of record according to the timeframe of starttime and endtime ,

for one example, the same person has 2 records, but the second row's starttime and endtime is contained in the firstrow's time frame

Member_id	StartDate	StartTime	EndTime
319458	1/9/2013 0:00	1/9/2013 17:00	1/9/2013 22:00
319458	1/9/2013 0:00	1/9/2013 18:00	1/10/2013 19:00

so i want to merge the above two row into one row and discard the second row, how to do it in Qlikview?

319458

1/9/2013 0:00

1/9/2013 17:00

1/9/2013 22:00

i want to all the cases like above example to merge into one row per the largest timeframe of starttime and endtime per same memberid on the same date

another example

Member_id	StartDate	StartTime	EndTime
319458	1/10/2013	1/9/2013 17:00	1/9/2013 22:00
319458	1/10/2013	1/9/2013 19:00	1/10/2013 23:00

merged into

Member_id	StartDate	StartTime	EndTime
319458	1/10/2013	1/9/2013 17:00	1/9/2013 23:00

Re: How to merge data

Fri, 01 Nov 2013 03:50:33 GMT

Hi Max,

In the script, do a group by Member_id and StartDate and calculate the min StartTime value and the max EndTime value.

For example,

Load Member_id,

StartDate,

min(StartTime) as StartTime,

max(EndTime) as EndTime

Resident Table

Group by

Member_id,

StartDate;

Karl

Re: How to merge data

Fri, 01 Nov 2013 07:00:29 GMT

sorry, i want to meger only timeframe that have crossed each other

Member_id	StartDate	StartTime	EndTime	ROW number
319458	1/10/2013	1/9/2013 17:00	1/9/2013 22:00	1
319458	1/10/2013	1/9/2013 19:00	1/10/2013 23:00	2
319458	1/10/2013	1/9/2013 7:00	1/9/2013 9:00	3

the row number 3 what i do not need to meger, the row 1,2 that need to merge into one due to cross of time

Re: How to merge data

Fri, 01 Nov 2013 09:48:57 GMT

who can help me

Re: How to merge data

preminqlik — Fri, 01 Nov 2013 10:46:22 GMT

hi there,

tab:

LOAD

MEMBER_ID, st.Date, StartDate, EndDate, RowNum

from ......

tab2:

NoConcatenate

load MEMBER_ID,st.Date,

min(RowNum) as RowNum

Resident tab Group by MEMBER_ID,st.Date;

left join

LOAD *

Resident tab;

DROP table tab;

find attachment

Regards

premhas

Re: How to merge data

Gysbert_Wassenaar — Fri, 01 Nov 2013 18:35:17 GMT

See attached qvw