Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rustyfishbones
Master II
Master II

Help with Table

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.

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

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

View solution in original post

2 Replies
antoniotiman
Master III
Master III

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

View solution in original post

rustyfishbones
Master II
Master II
Author

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