topic Re: Load variable value into new table field in QlikView

Load variable value into new table field

MikeH1983 — Sat, 16 Nov 2024 02:49:52 GMT

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;

Re: Load variable value into new table field

kfoudhaily — Mon, 05 Aug 2019 14:15:28 GMT

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?

Re: Load variable value into new table field

marcus_malinow — Mon, 05 Aug 2019 12:23:27 GMT

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;

Re: Load variable value into new table field

MikeH1983 — Mon, 05 Aug 2019 14:11:55 GMT

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;