Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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.
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;
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;
Hi kusha chawda,
Thanks for your time on in thsi, Still its not working..Thanks,
Thanks,
Chinnu.
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;
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.
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;
Hi Kushal,
Still Not getting the result.
Thanks,
Chinnu.