topic Re: reduce data from 2 lines per record to 1 in QlikView

reduce data from 2 lines per record to 1

rustyfishbones — Fri, 30 Mar 2018 16:03:24 GMT

Hi All,

I have the following Data

User Type LineNumber time trxno

1 A 1 0700 1

1 D 2 0800 1

I want to output to

User Type StartTime EndTime trxno

1 A 0700 0800 1

Whats the best solution to do this?

Re: reduce data from 2 lines per record to 1

MK_QSL — Fri, 30 Mar 2018 16:08:15 GMT

You want to remove Type D also... I mean the Minimum Time Type will be considered?

Also, do you have only two line number or for other users this line number may vary !!

Re: reduce data from 2 lines per record to 1

rustyfishbones — Fri, 30 Mar 2018 16:12:55 GMT

The type will not matter really, as long as I have StartTime and EndTime

Always only 2 lines for each user

Re: reduce data from 2 lines per record to 1

rustyfishbones — Fri, 30 Mar 2018 16:17:57 GMT

A is for Add and D is for Delete, basically represents the start and end of a transaction.

Thanks

Al.

Re: reduce data from 2 lines per record to 1

MK_QSL — Fri, 30 Mar 2018 16:18:33 GMT

SET TimeFormat='hhmm';


Data:
LOAD User, 
     Type, 
     LineNumber, 
     Time#(time,'hhmm') as time, 
     trxno
FROM
[Community_296628.xlsx]
(ooxml, embedded labels, table is Sheet1);


Right Join
Load User, Min(time) as time, Time(Min(time)) as StartTime, Time(Max(time)) as EndTime Resident Data Group By User;


Drop Fields time, LineNumber;

Re: reduce data from 2 lines per record to 1

rustyfishbones — Fri, 30 Mar 2018 16:26:40 GMT

Thank You