Load variable value into new table field

[MikeH1983]

Hello!

Can anyone help me figure out why this QlikSense script is not working?

I want to simply loop through each row of the [Master Calendar] table, and build a new table UnitHeadcount with a single HeadcountDate field. (I will eventually nest loops to take data from multiple tables which is why I am doing it this way).

The script successfully runs, and the TRACE statement outputs the date values, however no new table is created! Any idea what I am doing wrong`?

//script:

let counter = NoOfRows('Master Calendar');
 
for i=0 to $(counter)-1  
    LET vDate = Peek('MasterDate',$(i),'Master Calendar');
    TRACE $(vDate);
   
    UnitHeadcount:
        load
        $(vDate) as HeadcountDate;

NEXT i;

[kfoudhaily]

at first glimpse syntaxe seams ok, just try to delete the $ in the peek fonction.
can you you provide sample app so we can test?

[marcus_malinow · Accepted solution]

You could try amending your UnitHeadCount load to use AUTOGENERATE. You may also need to enclose $(vDate) in quotes.

  UnitHeadcount:
        load
        '$(vDate)' as HeadcountDate

AUTOGENERATE 1;

[MikeH1983]

Hi all

Thank for your help. Putting the variable name in quotes was the solution. 

FYI my problem was trying to put in one table a conditional count of rows from another table. I solved this by looping through dates, and on each loop loading to the new table a count of the other table, with the relevant conditions. This works now that the Count is only referring to one table. However it is very slow, so I am putting it aside for now. 

Here is the working code FYI:

personHeadcountTemp:
 Load [Personnel Number]
 Resident People;

Left Join (personHeadcountTemp)
 Load
  [Personnel Number],
     [Employment Status] as PTEmpStat,
     [Action Start Date] as PTActStart,
     [Action End Date] as PTActEnd
     Resident Actions;

Left Join (personHeadcountTemp)
 Load
  [Personnel Number],
     [Position Start Date] as PTPosStart,
     [Position End Date] as PTPosEnd,
     [Personnel Area] as PTArea,
     [Manager Name] as PTManager
     Resident [Position History];
    
let counter = NoOfRows('Master Calendar');
 

FOR i=0 to $(counter)-1 
    LET vDate = Peek('MasterDate',$(i),'Master Calendar');
     // TRACE $(vDate);
   
    UnitHeadcount:
        load
        '$(vDate)' as HeadcountDate,
        PTArea as [Personnel Area],
        count( distinct
   if ( PTEmpStat <> 'Withdrawn'
       and PTActStart <= '$(vDate)'
       and PTActEnd >= '$(vDate)'
       and PTPosStart <= '$(vDate)'
       and PTPosEnd >= '$(vDate)'              
           and PTHoursStart <= '$(vDate)'
       and PTHoursEnd >= '$(vDate)'              
       ,  [Personnel Number]) ) as Headcount
        Resident personHeadcountTemp
        Group by PTArea;

NEXT