topic Re: Facing issue with record number in QlikView

Facing issue with record number

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

Hi team,

I am having requirement which I need to fetch the records based on the record number

here is my sql condition which I need to convert into qlikview Please help me in coverting this

row_number() over (partition by incident_id order by coalesce(LAST_UPDATE_DATE,sysdate) desc) record_num,

After converting the above expression I need to fetch the records where record_num=1

Please can any one help me to resolve this..

Thanks in Advance..

Thanks,

Chinnu.

Re: Facing issue with record number

Anil_Babu_Samineni — Sat, 02 Sep 2017 08:36:21 GMT

May be this? First try Previous instead Peek

Sample:

LOAD RowNo() as Row_Number, LAST_UPDATE_DATE, Today() as Today_Date FROM TableName;

LEFT JOIN (Sample)

LOAD Row_Number,

if(LAST_UPDATE_DATE=Peek(LAST_UPDATE_DATE),Peek("Row Number")+1,1) as "Row Number"

ORDER BY LAST_UPDATE_DATE, Today_Date DESC;

DROP FIELD Row_Number;

Re: Facing issue with record number

chinnu123 — Sat, 02 Sep 2017 09:26:41 GMT

Hi Anil,

Thanks for your quick reply. Unfortunately its not working.

Here I need to get output as First record

for example I am having incident id's

Incident Id create date last update date Status rec no

inc56789 25 mar-2017 15 Apr-2017 Open 1

inc56789 20 mar-2017 29 Apr-2017 Pending 2

inc56789 15 jun-2017 20-jun 2017 closed 3

inc56789 25 jan-2017 19-jul-2017 review 4

Here in the above you can see different same incident id's with different dates and status.

Here I need to fetch the incident id which was having record no =1.

Note: Rec no is not a fiels we need to derive that field based on the above sql condition.

Hope I am clear

Thanks,

Chinnu.

Re: Facing issue with record number

Anil_Babu_Samineni — Sat, 02 Sep 2017 09:41:43 GMT

I am still not 100% sure, How you need. But try a luck

Welcome:

LOAD *, RecNo() as RecNo Inline [

Incident Id, create date , last update date , Status

inc56789, 25 mar-2017 , 15 Apr-2017 , Open

inc56789 , 20 mar-2017 , 29 Apr-2017 , Pending

inc56789 , 15 jun-2017 , 20-jun 2017 , closed

inc56789 , 25 jan-2017 , 19-jul-2017 , review

];

Final:

NoConcatenate

LOAD * Resident Welcome Where RecNo = 1 Order By RecNo;

DROP Table Welcome;

Re: Facing issue with record number

Kushal_Chawda — Sat, 02 Sep 2017 09:45:40 GMT

try this

Data:

LOAD

[Incident Id],

date(if(len(trim([last update date]))=0,today(),[last update date])) as Required_Date,

[create date],

[last update date],

Status

FROM table;

left join(Data)

LOAD [Incident Id],

date(min(Required_Date)) as Required_Date,

1 as isFirstDate

Resident Data

group by [Incident Id];

Final:

noconcatenate

LOAD *

Resident Data

where isFirstDate=1;

drop table Data;

Re: Facing issue with record number

chinnu123 — Sat, 02 Sep 2017 10:05:20 GMT

Hi kusha chawda,

Thanks for your time on in thsi, Still its not working..Thanks,

Thanks,

Chinnu.

Re: Facing issue with record number

Kushal_Chawda — Sat, 02 Sep 2017 10:15:29 GMT

It's giving me output which you have mentioned for below data

Data:

LOAD *,

date(if(len(trim([last update date]))=0,today(),[last update date])) as Required_Date Inline [

Incident Id, create date, last update date, Status

inc56789, 25-Mar-2017, 15-Apr-2017, Open,

inc56789, 20-Mar-2017, 29-Apr-2017, Pending

inc56789, 15-Jun-2017, 20-Jun-2017, closed

inc56789, 25-Jan-2017, 19-Jul-2017, review ];

left join(Data)

LOAD [Incident Id],

date(min(Required_Date)) as Required_Date,

1 as isFirstDate

Resident Data

group by [Incident Id];

Final:

noconcatenate

LOAD *

Resident Data

where isFirstDate=1;

drop table Data;

Re: Facing issue with record number

chinnu123 — Sat, 02 Sep 2017 10:36:39 GMT

Hi Kushal,

Yes, Your expression is working fine but here my problem is I am having a same date for the incident but time is different then it is fetching all the records with that date instead of fetching first record.

For example

inc567890 25-apr 2017 14:25 pm

inc567890 25-apr 2017 6:55 am

inc567890 25-apr 2017 11:01 am

Here in this case it is fetching all the three records instead of fetching first record. If I am having a same time and same date also I need to get only first record

Hope I am clear

Thanks,

Chinnu.

Re: Facing issue with record number

Kushal_Chawda — Sat, 02 Sep 2017 10:40:35 GMT

then try below

Data:

LOAD *,

timestamp(if(len(trim([last update date]))=0,today(),[last update date])) as Required_Date Inline [

Incident Id, create date, last update date, Status

inc56789, 25-Mar-2017, 15-Apr-2017, Open,

inc56789, 20-Mar-2017, 29-Apr-2017, Pending

inc56789, 15-Jun-2017, 20-Jun-2017, closed

inc56789, 25-Jan-2017, 19-Jul-2017, review ];

left join(Data)

LOAD [Incident Id],

timestamp(min(Required_Date)) as Required_Date,

1 as isFirstDate

Resident Data

group by [Incident Id];

Final:

noconcatenate

LOAD *

Resident Data

where isFirstDate=1;

drop table Data;

Re: Facing issue with record number

chinnu123 — Sat, 02 Sep 2017 11:16:14 GMT

Hi Kushal,

Still Not getting the result.

Thanks,

Chinnu.

Re: Facing issue with record number

Kushal_Chawda — Sat, 02 Sep 2017 11:22:43 GMT

Please provide the sample data with expected output?

Re: Facing issue with record number

effinty2112 — Sun, 03 Sep 2017 11:59:15 GMT

Hi Chinnu,

Try:

Data:

LOAD

[Incident Id],

Date(Date#([create date],'DD MMM-YYYY')) as [create date],

Date(Date#([last update date],'DD MMM-YYYY')) as [last update date],

Status;

LOAD * INLINE [

Incident Id, create date, last update date, Status

inc56789, 25 mar-2017, 15 Apr-2017, Open

inc56789, 20 mar-2017, 29 Apr-2017, Pending

inc56789, 15 jun-2017, 20 jun-2017, closed

inc56789, 25 jan-2017, 19 jul-2017, review

];

Inner Join(Data)

LOAD

[Incident Id],

[last update date]

Where AutoIncidentRecID =1;

LOAD

AutoNumber([last update date],[Incident Id]) as AutoIncidentRecID,

[Incident Id],

[last update date]

Resident Data

Order by [Incident Id],[last update date];

Returning

create date	Incident Id	last update date	Status
25/03/2017	inc56789	15/04/2017	Open

If you try it on a larger data set containing a number of Incident Id the script should return one line for each Id, the line with the earliest [last update date] for that id.

Please note I adjusted some of the text of the test data in my inline table. the format of the date field last update date was inconsistent and I made all in the format 'DD MMM-YYYY'.

Regards

Andrew