Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
brindlogcool
Creator III
Creator III

Loop through Table and create column

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:)

10 Replies
its_anandrjs

How you calculate "Duration" let me know.

Not applicable

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

brindlogcool
Creator III
Creator III
Author

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

CELAMBARASAN
Partner - Champion
Partner - Champion

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...

brindlogcool
Creator III
Creator III
Author

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

Anonymous
Not applicable

check this..

brindlogcool
Creator III
Creator III
Author

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

Anonymous
Not applicable

Now? I attached a .qvw file.

brindlogcool
Creator III
Creator III
Author

Sorry still i couldnt see any files attached to it