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: 
Not applicable

generating sequence for resident table

can anybody help me with the below scenario using Qlik Load Script.

I have table with the below structure

    

Ticket_nocreatedatechangeDatestatus
11/27/20141/28/2014Modify
11/27/20141/29/2014SUBMIT
11/27/20142/2/2014CLOSED
21/27/20141/28/2014Modify
21/27/20141/29/2014SUBMIT
32/2/20142/2/2014Modify
32/2/20148/8/2014SUBMIT
43/1/20143/3/2014Modify
58/8/20148/8/2014Modify
58/8/20148/10/2014SUBMIT
68/9/20148/9/2014Modify
68/9/20148/10/2014SUBMIT
68/9/201411/11/2014CLOSED

now I have created a resident table using qlik LOAD with the below structure (i.e. de-normalizing the above one )

Resident Table :

Ticket_noDateStatus
11/27/2014Created
11/28/2014Modify
11/29/2014SUBMIT
12/2/2014Closed
21/27/2014Created
21/28/2014Modify
21/29/2014SUBMIT
32/2/2014Created
32/2/2014Modify
38/8/2014SUBMIT
43/1/2014Created
43/1/2014Modify
58/8/2014Created
58/8/2014Modify
510/8/2014SUBMIT
68/9/2014Created
68/9/2014Modify
610/8/2014SUBMIT
611/11/2014Closed

Now I want to create a sequence for each ticket.... output should come like this

    

sequenceSKU DateStatus
111/27/2014Created
211/28/2014Modify
311/29/2014SUBMIT
412/2/2014Closed
121/27/2014Created
221/28/2014Modify
321/29/2014SUBMIT
132/2/2014Created
232/2/2014Modify
338/8/2014SUBMIT
143/1/2014Created
243/1/2014Modify
158/8/2014Created
258/8/2014Modify
3510/8/2014SUBMIT
168/9/2014Created
268/9/2014Modify
3610/8/2014SUBMIT
4611/11/2014Closed

If I have the same Data in SQL then I can use the below query to the sequence

select ROW_NUMBER() OVER (partition by ticket_no order by date asc)

         AS sequence,Ticket_no,Date,Status

     from table

1 Solution

Accepted Solutions
sunny_talwar

Try this:

Table:

LOAD DISTINCT Ticket_no,

    createdate as Date,

    'Created' as status

FROM

[https://community.qlik.com/thread/204549]

(html, codepage is 1252, embedded labels, table is @1);

Concatenate(Table)

LOAD Ticket_no,

    changeDate as Date,

    status

FROM

[https://community.qlik.com/thread/204549]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD *,

  If(Ticket_no = Peek('Ticket_no'), RangeSum(Peek('sequence'), 1), 1) as sequence

Resident Table

Order By Ticket_no, Date;

DROP Table Table;

Capture.PNG

View solution in original post

2 Replies
sunny_talwar

Try this:

Table:

LOAD DISTINCT Ticket_no,

    createdate as Date,

    'Created' as status

FROM

[https://community.qlik.com/thread/204549]

(html, codepage is 1252, embedded labels, table is @1);

Concatenate(Table)

LOAD Ticket_no,

    changeDate as Date,

    status

FROM

[https://community.qlik.com/thread/204549]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD *,

  If(Ticket_no = Peek('Ticket_no'), RangeSum(Peek('sequence'), 1), 1) as sequence

Resident Table

Order By Ticket_no, Date;

DROP Table Table;

Capture.PNG

Not applicable
Author

Thanks sunny I have tired the below one but didn't wokredout... and ur solution works perfect

If(Ticket_no= Peek('Ticket_no'), 1 + Peek('Sequence'), 1) as Sequence