topic Re: Need help in QlikView

Need help

chinnu123 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Need help

vinieme12 — Sun, 03 Sep 2017 10:27:49 GMT

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;

Re: Need help

chinnu123 — Sun, 03 Sep 2017 10:44:24 GMT

Hi Vineeth,

Thanks for your quick reply, Its not working.

Thanks,

Chinnu.

Re: Need help

effinty2112 — Sun, 03 Sep 2017 11:20:54 GMT

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

Re: Need help

chinnu123 — Sun, 03 Sep 2017 11:56:21 GMT

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.

Re: Need help

effinty2112 — Sun, 03 Sep 2017 12:15:38 GMT

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

Re: Need help

chinnu123 — Sun, 03 Sep 2017 13:00:31 GMT

Hi Andrew,

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

Thanks,

Chinnu.

Re: Need help

effinty2112 — Sun, 03 Sep 2017 14:26:42 GMT

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

Re: Need help

vinieme12 — Sun, 03 Sep 2017 18:31:52 GMT

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;

Re: Need help

chinnu123 — Sun, 03 Sep 2017 18:38:50 GMT

Hi vineeth,

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

Thanks,

Chinnu.

Re: Need help

vinieme12 — Sun, 03 Sep 2017 18:46:49 GMT

can you post your Script?

Re: Need help

vinieme12 — Sun, 03 Sep 2017 18:49:52 GMT

i'm able to get the correct output