Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
rustyfishbones
Honored Contributor 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.

Tags (4)
1 Solution

Accepted Solutions
antoniotiman
Honored Contributor III

Re: Help with Table

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
Honored Contributor III

Re: Help with Table

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
Honored Contributor II

Re: Help with Table

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