Facing issue with record number

[chinnu123]

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.

[Kushal_Chawda]

Please provide the sample data with expected output?

[effinty2112]

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