topic Re: generating sequence for resident table in App Development

generating sequence for resident table

Mon, 08 Feb 2016 04:29:04 GMT

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

I have table with the below structure

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

Resident Table :

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

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

sunny_talwar — Mon, 08 Feb 2016 09:36:03 GMT

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;

Tue, 09 Feb 2016 04:53:07 GMT

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