Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
rustyfishbones
Master II
Master 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?

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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;

View solution in original post

5 Replies
MK_QSL
MVP
MVP

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
Master II
Master II
Author

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

Always only 2 lines for each user

rustyfishbones
Master II
Master II
Author

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

Thanks

Al.

MK_QSL
MVP
MVP

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;

View solution in original post

rustyfishbones
Master II
Master II
Author

Thank You