Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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

Re: generating sequence for resident table

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

2 Replies
MVP
MVP

Re: generating sequence for resident table

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

Re: generating sequence for resident table

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