Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to retrieve previous date's cases by county. So for san Francisco, I need to receive value of 3000. When I use peek function, I receive value of 5100. This is a very simplified data set. Actual data set has 100,000 rows with a data point for multiple counties for each date. Any thoughts are greatly appreciated.
date county cases
4/1/20 New York 5000
4/1/20 San Francisco 3000
4/2/20 New York 5100
4/2/20 San Francisco 3200
Firstly you need to sort the table. Try the code below:
tmp1:
NoConcatenate LOAD *
Resident tmp
Order By country asc, date asc;
DROP Table tmp;
tmp:
LOAD *
,If(country = Peek(country), Peek(cases), cases) as cases2
Resident tmp1;
DROP Table tmp1;
AsOfTable could be a nice approach here:
https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130
Create it daily wise.
then you can use an easy expression.
sum({$<AsOf_Days_Ago={"1"}>} cases) //cases previous date
Thank you for the advice. I am still missing something. Below is the script I am using to ensure table is being sorted. But table still seems to be unsorted.
Really need QVW file to be able to dig into things at this point, only other thing would be to review the Help to be sure something has not been missed there, but I cannot see anything, but you can review yourself too:
The only other thing may be a known issue in the release you are running, so if you are not on the most current SR, I would update and try with that release too just to be sure you are not running into something that may have been addressed there.
Regards,
Brett