Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
can anybody help me with the below scenario using Qlik Load Script.
I have table with the below structure
Ticket_no | createdate | changeDate | status |
1 | 1/27/2014 | 1/28/2014 | Modify |
1 | 1/27/2014 | 1/29/2014 | SUBMIT |
1 | 1/27/2014 | 2/2/2014 | CLOSED |
2 | 1/27/2014 | 1/28/2014 | Modify |
2 | 1/27/2014 | 1/29/2014 | SUBMIT |
3 | 2/2/2014 | 2/2/2014 | Modify |
3 | 2/2/2014 | 8/8/2014 | SUBMIT |
4 | 3/1/2014 | 3/3/2014 | Modify |
5 | 8/8/2014 | 8/8/2014 | Modify |
5 | 8/8/2014 | 8/10/2014 | SUBMIT |
6 | 8/9/2014 | 8/9/2014 | Modify |
6 | 8/9/2014 | 8/10/2014 | SUBMIT |
6 | 8/9/2014 | 11/11/2014 | CLOSED |
now I have created a resident table using qlik LOAD with the below structure (i.e. de-normalizing the above one )
Resident Table :
Ticket_no | Date | Status |
1 | 1/27/2014 | Created |
1 | 1/28/2014 | Modify |
1 | 1/29/2014 | SUBMIT |
1 | 2/2/2014 | Closed |
2 | 1/27/2014 | Created |
2 | 1/28/2014 | Modify |
2 | 1/29/2014 | SUBMIT |
3 | 2/2/2014 | Created |
3 | 2/2/2014 | Modify |
3 | 8/8/2014 | SUBMIT |
4 | 3/1/2014 | Created |
4 | 3/1/2014 | Modify |
5 | 8/8/2014 | Created |
5 | 8/8/2014 | Modify |
5 | 10/8/2014 | SUBMIT |
6 | 8/9/2014 | Created |
6 | 8/9/2014 | Modify |
6 | 10/8/2014 | SUBMIT |
6 | 11/11/2014 | Closed |
Now I want to create a sequence for each ticket.... output should come like this
sequence | SKU | Date | Status |
1 | 1 | 1/27/2014 | Created |
2 | 1 | 1/28/2014 | Modify |
3 | 1 | 1/29/2014 | SUBMIT |
4 | 1 | 2/2/2014 | Closed |
1 | 2 | 1/27/2014 | Created |
2 | 2 | 1/28/2014 | Modify |
3 | 2 | 1/29/2014 | SUBMIT |
1 | 3 | 2/2/2014 | Created |
2 | 3 | 2/2/2014 | Modify |
3 | 3 | 8/8/2014 | SUBMIT |
1 | 4 | 3/1/2014 | Created |
2 | 4 | 3/1/2014 | Modify |
1 | 5 | 8/8/2014 | Created |
2 | 5 | 8/8/2014 | Modify |
3 | 5 | 10/8/2014 | SUBMIT |
1 | 6 | 8/9/2014 | Created |
2 | 6 | 8/9/2014 | Modify |
3 | 6 | 10/8/2014 | SUBMIT |
4 | 6 | 11/11/2014 | Closed |
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
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;
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;
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