topic Re: peek with multiple fields in QlikView

peek with multiple fields

spags1965 — Fri, 17 Jul 2020 05:10:54 GMT

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

Re: peek with multiple fields

MindaugasBacius — Fri, 17 Jul 2020 05:50:18 GMT

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;

Re: peek with multiple fields

zhadrakas — Fri, 17 Jul 2020 06:00:27 GMT

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

Re: peek with multiple fields

spags1965 — Fri, 17 Jul 2020 14:01:56 GMT

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;

Re: peek with multiple fields

Brett_Bleess — Wed, 29 Jul 2020 11:40:46 GMT

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/ScriptRegularStatements/Load.htm

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