peek with multiple fields

[spags1965]

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

[MindaugasBacius]

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;

[zhadrakas]

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

[spags1965]

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.

tmp:
LOAD date#(date, 'YYYY-MM-DD') as date,
     text(county) as county,
     state,
     fips,
     cases,
     deaths
  
FROM
[N:\data\covid-19-data-master\us-counties.xls]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq;

tmp1:
NoConcatenate LOAD *
Resident tmp
//Order By county asc;
Order By county asc, date desc;
DROP Table tmp;

[Brett_Bleess]

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:

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/Scrip...

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