Need help

[chinnu123]

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.

[vinieme12]

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;

[chinnu123]

Hi Vineeth,

Thanks for your quick  reply, Its not working.

Thanks,

Chinnu.

[effinty2112]

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

[chinnu123]

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.

[effinty2112]

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

[chinnu123]

Hi Andrew,

Still No luck Its taking a lot of time to reload. I think its because of AUTONUMBER

Thanks,

Chinnu.

[effinty2112]

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

[vinieme12]

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;

[chinnu123]

Hi vineeth,

I already tried by using pick but unfortunately it's not giving correct result

Thanks,

Chinnu.