Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

rustyfishbones
Honored Contributor II

reduce data from 2 lines per record to 1

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?

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: reduce data from 2 lines per record to 1

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;

5 Replies
MVP
MVP

Re: reduce data from 2 lines per record to 1

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 !!

rustyfishbones
Honored Contributor II

Re: reduce data from 2 lines per record to 1

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

Always only 2 lines for each user

rustyfishbones
Honored Contributor II

Re: reduce data from 2 lines per record to 1

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

Thanks

Al.

MVP
MVP

Re: reduce data from 2 lines per record to 1

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;

Highlighted
rustyfishbones
Honored Contributor II

Re: reduce data from 2 lines per record to 1

Thank You