Here are my statements, starting from the relevant portion:
| eval yr="$form.yr$" | eval quarter="$form.quarter$" | eval yr=if("$form.quarter$"="Q1" OR "$form.quarter$"="Q2",yr-1,yr) | eval monthSearch=if("$form.quarter$"="Q1",'yr'+"07 "+'yr'+"08 "+'yr'+"09 ",monthSearch) | eval monthSearch=if("$form.quarter$"="Q2",'yr'+"10 "+'yr'+"11 "+'yr'+"12 ",monthSearch) | eval monthSearch=if("$form.quarter$"="Q3",'yr'+"01 "+'yr'+"02 "+'yr'+"03 ",monthSearch) | eval monthSearch=if("$form.quarter$"="Q4",'yr'+"04 "+'yr'+"05 "+'yr'+"06 ",monthSearch) | makemv monthSearch | eval monthSearch1=mvindex(monthSearch,0) | eval monthSearch2=mvindex(monthSearch,1) | eval monthSearch3=mvindex(monthSearch,2) | lookup closures "fullName" AS "fullName"
The following is what doesn't work:
| eval month1='monthSearch1' | eval month2='monthSearch2' | eval month3='monthSearch3' |** fillnull value=0 month1 month2 month3
Assume $form.quarter$=Q1 and $form.yr$=2015
The lookup "closures" contains the following info:
fullname,201507,201508,201509,201510,201511,201512
Adam Anderson,12,10,15,,37,11
Bob Briggs,,,4,21,,15
Cam Carson,10,25,31,22,16,1
I want an intermediate table that looks like:
fullName,monthSearch1,monthSearch2,monthSearch3,201507,201508,201509,month1,month2,month3
Adam Anderson,201507,201508,201509,12,10,15,12,10,15
... View more