topic Re: Help with Table in QlikView

Help with Table

rustyfishbones — Tue, 19 Sep 2017 21:29:47 GMT

Hi All,

I have the following table of Data

adjustment-type date-added line-number Time-added trx-no trx-type

A 30/08/2016 1 0724 1 Z2

D 30/08/2016 2 1314 1 Z2

But I want to use the above table to create the below table

date-added line-number Time-added trx-no trx-type Time-Stopped time-diff

30/08/2016 1 0724 1 Z2 1314 350

I was wondering can someone can suggest the best way to do it.

Thanks

Alan.

Re: Help with Table

antoniotiman — Tue, 19 Sep 2017 22:11:29 GMT

Hi Alan,

maybe this

Temp:
LOAD adjustment_type,date_added,line_number,
Time(Time#(Time_added,'hhmm'),'hhmm') as Time_added,trx_no,trx_type Inline [
adjustment_type, date_added, line_number, Time_added, trx_no, trx_type
A , 30/08/2016, 1, 0724, 1, Z2
D , 30/08/2016, 2, 1314, 1, Z2];
Temp1:
LOAD *,Interval(Time_stopped-Time_added,'mm') as time_diff;
LOAD date_added,line_number,Time_added,trx_no,trx_type,
If(date_added=Peek(date_added) and trx_no=Peek(trx_no) and trx_type=Peek(trx_type),Peek(Time_added)) as Time_stopped
Resident Temp
Order By trx_type,trx_no,date_added,line_number desc;
NoConcatenate LOAD * Resident Temp1 Where line_number = 1;
Drop Tables Temp,Temp1;

Regards,

Antonio

Re: Help with Table

rustyfishbones — Tue, 19 Sep 2017 22:33:45 GMT

Perfect thanks Antonio, I was using PEEK but not using Order By