Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
I have a requirement to fetch unique incident Id's based on the last updated date.
First I need to find a record num based on incident id and Lastupdated date.
for example For Incident Id INC001123 we are having 5 lastupdated dates so here we need to take recent last updated date(i.e 9/1/2017) as first record and 8/1/2017 as record 2 in the same way remaining records must be stored in record num field.
here i am providing my sql expression based on this i need to write in qlik. Can anyone please help me in this
For row_number() over (partition by Incident Id order by Lastupdated date desc)
Incident Id | Lastupdated date | Status |
INC001123 | 5/22/2017 1:11 | open |
INC001123 | 5/19/2017 9:17 | review |
INC001123 | 7/26/2017 1:11 | closed |
INC001123 | 8/1/2017 1:11 | review |
INC001123 | 9/1/2017 1:11 | Approved |
INC001124 | 6/26/2017 1:11 | open |
INC001124 | 8/27/2017 1:11 | open |
INC001124 | 2/28/2017 1:11 | open |
INC001124 | 4/29/2017 1:11 | open |
INC001124 | 7/30/2017 1:11 | open |
Thanks,
Chinnu.
LOAD IncidentID ,
LastUpd_Date,
Status
Autonumber(IncidentID & LastUpd_Date) as Incident_InstanceNumber
RESIDENT TABLENAME
ORder by IncidentID Desc;
Please note Orderby only works with Resident tables;
As an alternative you can do the Orderby in the SQL SELECT statement and the Autonumber in LOAD statement
something like below
LOAD IncidentID ,
LastUpd_Date,
Status
Autonumber(IncidentID & LastUpd_Date) as Incident_InstanceNumber ;
SQL SELECT IncidentID , LastUpd_Date, Status
Order by LastUpd_Date DESC;
Hi Vineeth,
Thanks for your quick reply, Its not working.
Thanks,
Chinnu.
Hi Chinnu,
Is this what you're looking for:
RecordID | Incident Id | Lastupdated date | Status |
---|---|---|---|
1 | INC001123 | 01/09/2017 01:11:00 | Approved |
2 | INC001123 | 01/08/2017 01:11:00 | review |
3 | INC001123 | 26/07/2017 01:11:00 | closed |
4 | INC001123 | 22/05/2017 01:11:00 | open |
5 | INC001123 | 19/05/2017 09:17:00 | review |
1 | INC001124 | 27/08/2017 01:11:00 | open |
2 | INC001124 | 30/07/2017 01:11:00 | open |
3 | INC001124 | 26/06/2017 01:11:00 | open |
4 | INC001124 | 29/04/2017 01:11:00 | open |
5 | INC001124 | 28/02/2017 01:11:00 | open |
Data:
LOAD
[Incident Id],
Timestamp(Alt(Timestamp#([Lastupdated date],'M/DD/YYYY h:mm'), Timestamp#([Lastupdated date],'MM/DD/YYYY hh:mm'))) as [Lastupdated date],
Status
INLINE [
Incident Id, Lastupdated date, Status
INC001123, 5/22/2017 1:11, open
INC001123, 5/19/2017 9:17, review
INC001123, 7/26/2017 1:11, closed
INC001123, 08/01/2017 01:11, review
INC001123, 09/01/2017 01:11, Approved
INC001124, 6/26/2017 1:11, open
INC001124, 8/27/2017 1:11, open
INC001124, 2/28/2017 1:11, open
INC001124, 4/29/2017 1:11, open
INC001124, 7/30/2017 1:11, open
];
Left Join(Data)
LOAD
AutoNumber([Lastupdated date],[Incident Id]) as RecordID,
[Incident Id],
[Lastupdated date]
Resident Data
Order by [Incident Id],[Lastupdated date] desc;
I had to use this line
Timestamp(Alt(Timestamp#([Lastupdated date],'M/DD/YYYY h:mm'), Timestamp#([Lastupdated date],'MM/DD/YYYY hh:mm'))) as [Lastupdated date],
To give the [Lastupdated date] field a consistent format.
If you prefer you can do it in the front end without the left join statement
Incident Id | Lastupdated date | Status | RecordID |
---|---|---|---|
INC001123 | 01/09/2017 01:11:00 | Approved | 1 |
INC001123 | 01/08/2017 01:11:00 | review | 2 |
INC001123 | 26/07/2017 01:11:00 | closed | 3 |
INC001123 | 22/05/2017 01:11:00 | open | 4 |
INC001123 | 19/05/2017 09:17:00 | review | 5 |
INC001124 | 27/08/2017 01:11:00 | open | 1 |
INC001124 | 30/07/2017 01:11:00 | open | 2 |
INC001124 | 26/06/2017 01:11:00 | open | 3 |
INC001124 | 29/04/2017 01:11:00 | open | 4 |
INC001124 | 28/02/2017 01:11:00 | open | 5 |
Sort by [Incident Id], then by [Lastupdated date] numeric descending and the expression labelled in the table as RecordID is simply RowNo().
Regards
Andrew
Hi Andrew,
Thanks for Your time on this, yes this is what I am expecting but When I am reloading the application it taking too long to reload. it is having millions of records it is because of Autonumber? And I need only incidents where record id=1
Thanks,
Chinnu.
Hi Chinnu,
I don't think it is the Autonumber function that is making things slow but the left join statement since this involves a big sort. You can avoid the left join by adding an Autonumber line to your script that loads from your source. If you're using a preceding load then sort on the SQL statement by [Incident Id] then by [Lastupdated date] descending.
Now add a line :
AutoNumber([Lastupdated date],[Incident Id]) as RecordID,
to the top half of the preceding load.
Good luck
Andrew
Hi Andrew,
Still No luck Its taking a lot of time to reload. I think its because of AUTONUMBER
Thanks,
Chinnu.
Hi Chinnu,
Are you doing the sorting on the server side by having the sort statement as part of the SQL Select half of a preceding load?
Cheers
Andrew
Yes Autonumber() will affect the load time, because it will check each Row to assign the value and it will happen for every unique value in that field.
Instead of Autonumber() try with Peek(), this should be considerably faster
Data:
LOAD
[Incident Id],
Timestamp(Timestamp#([Lastupdated date],'MM/DD/YYYY hh:mm')) as [Lastupdated date],
Status
INLINE [
Incident Id, Lastupdated date, Status
INC001123, 05/22/2017 1:11, open
INC001123, 05/19/2017 9:17, review
INC001123, 07/26/2017 1:11, closed
INC001123, 08/01/2017 01:11, review
INC001123, 09/01/2017 01:11, Approved
INC001124, 06/26/2017 1:11, open
INC001124, 08/27/2017 1:11, open
INC001124, 02/28/2017 1:11, open
INC001124, 04/29/2017 1:11, open
INC001124, 07/30/2017 1:11, open
];
Left Join(Data)
LOAD
if([Incident Id]=Previous([Incident Id]),Rangesum(Peek(RecordID),1),1) as RecordID,
[Incident Id],
[Lastupdated date]
Resident Data
Order by [Incident Id],[Lastupdated date] desc;
Hi vineeth,
I already tried by using pick but unfortunately it's not giving correct result
Thanks,
Chinnu.