topic Fetch Previous record by date when there is no record for current date in QlikView

Fetch Previous record by date when there is no record for current date

hitha1512 — Tue, 03 Sep 2019 08:21:44 GMT

Hi Team,

Please find the attached QVW that has the raw data.

Requirement:

assuming current date is 03/08/2019, the rate for CAD has not flown in for this date. In such case, I need to pull value from date that last had the value along with its date(in new column). Also there are chances of file being missed for a day, in that case we will have to pick data from previous date to this date.

Please find below current output and the required output.

This needs to be dynamic. The rates are received for various currencies on daily basis.

Current Output:

Currency	Value	Date
CAD	11	1/08/2019
CAD	11.5	2/08/2019
CAD	20.9	5/08/2019

Required output:

Currency	Value	Date	New column for date to show from which date it has been picked
CAD	11	1/08/2019	1/08/2019
CAD	11.5	2/08/2019	2/08/2019
CAD	11.5	3/08/2019	2/08/2019
CAD	11.5	4/08/2019	2/08/2019
CAD	20.9	5/08/2019	5/08/2019

thank you

Regards,

Hitha Dhani

Re: Fetch Previous record by date when there is no record for current date

miskinmaz — Tue, 03 Sep 2019 07:21:27 GMT

You can follow the below link https://community.qlik.com/t5/Qlik-Design-Blog/Creating-a-Date-Interval-from-a-Single-Date/ba-p/1467840

Re: Fetch Previous record by date when there is no record for current date

Arthur_Fong — Tue, 03 Sep 2019 08:48:35 GMT

Refer qvw below if you couldn't get the desired result:

Thanks and regards,

Arthur Fong

Re: Fetch Previous record by date when there is no record for current date

hitha1512 — Tue, 03 Sep 2019 09:16:28 GMT

thanks a lot for the quick response 🙂 appreciate it !

Just another small bit.... is there a way i can show from which previous date the value was taken in another date column ?

Regards,

Hitha Dhani

Re: Fetch Previous record by date when there is no record for current date

Arthur_Fong — Tue, 03 Sep 2019 10:01:44 GMT

Add a new logic(DatePicked) similar to the New column:

Thanks and regards,

Arthur Fong

Re: Fetch Previous record by date when there is no record for current date

hitha1512 — Tue, 03 Sep 2019 10:08:49 GMT

Awesome! Thanks a ton again 🙂 that worked.