Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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

rustyfishbones
Master II
Master II
Author

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