Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
spags1965
Contributor
Contributor

peek with multiple fields

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

 

4 Replies
MindaugasBacius
Partner
Partner

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
Specialist II
Specialist II

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
Contributor
Contributor
Author

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;
 
Capture.JPG
Brett_Bleess
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.