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.

[Anil_Babu_Samineni]

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;

[chinnu123]

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.

[Anil_Babu_Samineni]

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;

[Kushal_Chawda]

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;

[chinnu123]

Hi kusha chawda,

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

Thanks,

Chinnu.

[Kushal_Chawda]

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;

[chinnu123]

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.

[Kushal_Chawda]

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;

[chinnu123]

Hi Kushal,

Still Not getting the result.

Thanks,

Chinnu.