topic Re: Loop through Table and create column in QlikView

Loop through Table and create column

brindlogcool — Wed, 09 Apr 2014 10:25:49 GMT

Hi,

How to create the new column by looping through an table in the Load script.

Consider the below example

i have this table

Key Name StartTime EndTime Timestamp Type

A-12/3/2013 12:12:35-12/3/2013 2:12:35 A 12/3/2013 12:12:35 12/3/2013 2:12:35 12/3/2013 12:12:36 Started

A-12/3/2013 12:12:35-12/3/2013 2:12:35 A 12/3/2013 12:12:35 12/3/2013 2:12:35 12/3/2013 12:12:38 Started1

A-12/3/2013 12:12:35-12/3/2013 2:12:35 A 12/3/2013 12:12:35 12/3/2013 2:12:35 12/3/2013 12:12:40 Started2

A-12/3/2013 12:12:35-12/3/2013 2:12:35 A 12/3/2013 12:12:35 12/3/2013 2:12:35 12/3/2013 12:12:42 Started3

A-12/3/2013 12:12:35-12/3/2013 2:12:35 A 12/3/2013 12:12:35 12/3/2013 2:12:35 12/3/2013 12:12:44 Started4

A-12/3/2013 12:12:35-12/3/2013 2:12:35 A 12/3/2013 12:12:35 12/3/2013 2:12:35 12/3/2013 12:12:50 Started6

B-12/3/2013 12:12:35-12/3/2013 2:12:40 B 12/3/2013 12:12:35 12/3/2013 2:15:40 12/3/2013 12:12:41 Started

B-12/3/2013 12:12:35-12/3/2013 2:12:40 B 12/3/2013 12:12:35 12/3/2013 2:15:40 12/3/2013 12:12:43 Started1

B-12/3/2013 12:12:35-12/3/2013 2:12:40 B 12/3/2013 12:12:35 12/3/2013 2:15:40 12/3/2013 12:12:44 Started2

B-12/3/2013 12:12:35-12/3/2013 2:12:40 B 12/3/2013 12:12:35 12/3/2013 2:15:40 12/3/2013 12:12:46 Started3

B-12/3/2013 12:12:35-12/3/2013 2:12:40 B 12/3/2013 12:12:35 12/3/2013 2:15:40 12/3/2013 12:12:46 Started4

B-12/3/2013 12:12:35-12/3/2013 2:12:40 B 12/3/2013 12:12:35 12/3/2013 2:15:40 12/3/2013 12:12:50 Started6

I want to create the new table with additional column duration as bringing the next row

Key Name StartTime EndTime Timestamp Type Duration

A-12/3/2013 12:12:35-12/3/2013 2:12:35 A 12/3/2013 12:12:35 12/3/2013 2:12:35 12/3/2013 12:12:36 Started 12/3/2013 12:12:38

A-12/3/2013 12:12:35-12/3/2013 2:12:35 A 12/3/2013 12:12:35 12/3/2013 2:12:35 12/3/2013 12:12:38 Started1 12/3/2013 12:12:40

A-12/3/2013 12:12:35-12/3/2013 2:12:35 A 12/3/2013 12:12:35 12/3/2013 2:12:35 12/3/2013 12:12:40 Started2 12/3/2013 12:12:42

A-12/3/2013 12:12:35-12/3/2013 2:12:35 A 12/3/2013 12:12:35 12/3/2013 2:12:35 12/3/2013 12:12:42 Started3 12/3/2013 12:12:44

A-12/3/2013 12:12:35-12/3/2013 2:12:35 A 12/3/2013 12:12:35 12/3/2013 2:12:35 12/3/2013 12:12:44 Started4 12/3/2013 12:12:50

A-12/3/2013 12:12:35-12/3/2013 2:12:35 A 12/3/2013 12:12:35 12/3/2013 2:12:35 12/3/2013 12:12:50 Started6 12/3/2013 12:12:50

B-12/3/2013 12:12:35-12/3/2013 2:12:40 B 12/3/2013 12:12:35 12/3/2013 2:15:40 12/3/2013 12:12:41 Started 12/3/2013 12:12:43

B-12/3/2013 12:12:35-12/3/2013 2:12:40 B 12/3/2013 12:12:35 12/3/2013 2:15:40 12/3/2013 12:12:43 Started1 12/3/2013 12:12:44

B-12/3/2013 12:12:35-12/3/2013 2:12:40 B 12/3/2013 12:12:35 12/3/2013 2:15:40 12/3/2013 12:12:44 Started2 12/3/2013 12:12:46

B-12/3/2013 12:12:35-12/3/2013 2:12:40 B 12/3/2013 12:12:35 12/3/2013 2:15:40 12/3/2013 12:12:46 Started3 12/3/2013 12:12:46

B-12/3/2013 12:12:35-12/3/2013 2:12:40 B 12/3/2013 12:12:35 12/3/2013 2:15:40 12/3/2013 12:12:46 Started4 12/3/2013 12:12:50

B-12/3/2013 12:12:35-12/3/2013 2:12:40 B 12/3/2013 12:12:35 12/3/2013 2:15:40 12/3/2013 12:12:50 Started6 12/3/2013 12:12:50

I understand we can get this done using the Peek function but couldnt get it done.it would very helpful if somebody could help me on this, I got stuck on this last two days:)

Re: Loop through Table and create column

its_anandrjs — Wed, 09 Apr 2014 10:35:14 GMT

How you calculate "Duration" let me know.

Re: Loop through Table and create column

Wed, 09 Apr 2014 10:41:07 GMT

What logic you used to find the Duration time in your expected output table field Duration

Re: Loop through Table and create column

brindlogcool — Wed, 09 Apr 2014 10:43:08 GMT

Duration is the Next Row value, I will check the Current row Key field value to next row Key Field value . If both are same then Duration will be the Next row Timestamp Value else EndTime Column Value.

Logically it will be like this

IF(Key=Peek('Key',RowNo()+1),Peek('Timestamp',RowNo()+1,EndTime) as Duration

Re: Loop through Table and create column

CELAMBARASAN — Wed, 09 Apr 2014 10:47:58 GMT

Hi,

Create a rownumber field in the Raw data like

Load

RowNo() AS RowNumber

*....

then use Order by over the field RowNumber in descending and get the values using Previous function

Load

Previous(Timestamp) AS Duration...

Re: Loop through Table and create column

brindlogcool — Wed, 09 Apr 2014 10:50:41 GMT

Hi Celambarasan,

Thanks for the response. But it is not always the next row value you will need compare the key value and if it doesnt match then you should have the endtime as duration else the nextrow value..Logically like this

IF(Key=Peek('Key',RowNo()+1),Peek('Timestamp',RowNo()+1,EndTime) as Duration

Re: Loop through Table and create column

Anonymous — Wed, 09 Apr 2014 13:12:12 GMT

check this..

Re: Loop through Table and create column

brindlogcool — Wed, 09 Apr 2014 13:15:34 GMT

Is there any link you attached or sample file... I couldnt see anything ...

Re: Re: Loop through Table and create column

Anonymous — Wed, 09 Apr 2014 13:35:04 GMT

Now? I attached a .qvw file.

Re: Re: Loop through Table and create column

brindlogcool — Wed, 09 Apr 2014 13:37:50 GMT

Sorry still i couldnt see any files attached to it

Re: Re: Re: Loop through Table and create column

Anonymous — Wed, 09 Apr 2014 14:07:08 GMT

Look this..

load * Inline
[
key,endtime,timestamp

A-12/3/2013 12:12:35-12/3/2013 2:12:35,12/3/2013 2:12:35,12/3/2013 12:12:36
A-12/3/2013 12:12:35-12/3/2013 2:12:35 ,12/3/2013 2:12:35,12/3/2013 12:12:38
B-12/3/2013 12:12:35-12/3/2013 2:12:40,12/3/2013 2:15:40,12/3/2013 12:12:41
];

T2:
Load *,
peek(key,RowNo(),'T')as peek,
if(key=peek(key,RowNo(),'T'),peek(timestamp,RowNo(),'T'),endtime) as Duration
Resident T;

DROP Table T;

HTH,

Ravi