Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

For Each statement Error

hello guys ,

I am getting error plz find screenshot . ( unexpected token error)

here my code kindly let me know is there any problem with the script .

Set vCounter = 0;

For Each vIdProject2018 in FieldValueList('Id Project');

   

Projectflow32018:

Load

PIdate2018,

NewDateLOI,

NumberofFlow3hours*Projectspermonth as "Hours2018"

Resident [FinalMasterProject]

where "Id Project" = $(vIdProject2018);

  

Let vPIdate2018= Num(Peek('PIdate2018', 0, 'Projectflow32018'));

LET vNewDateLOI = Num(Peek('NewDateLOI', 0, 'Projectflow32018'));

LET vHours2018 = Num(Peek('Hours2018', 0, 'Projectflow32018'));

  For vDate2018= Num($(vPIdate2018)) to Num($(vNewDateLOI))

  Data:

  Load

  $(vIdProject2018) as "Id Project",

  $(vDate2018) as Projectdatesflow32018,

      Num($(vHours2018))/(Num($(vNewDateLOI)) - Num($(vPIdate2018)))  as flow3Hoursspent2018

          AutoGenerate 1;

      Next vDate2018

  

    Drop Table Projectflow32018;

   

Next vIdProject2018

Thanks,

Ravi

1 Solution

Accepted Solutions
marcus_sommer

I would check within a separate load what your fields NumberofFlow3hours and Projectspermonth really contain - are there really values and if are they numbers too? A check on a number could be done with:

isnum(NumberofFlow3hours)

and if the value looked like a number but isnum() returned false the values needs to be converted with num#().

- Marcus

View solution in original post

8 Replies
petter
Partner - Champion III
Partner - Champion III

The error message indicates that the $-sign expansion of vNewDateLOI variable is not numeric. If it was you wouldnt get an error message. Most likely the variable contains a NULL. So you can safeguard by wrapping the part after the to in an Alt()-function like this:

  Alt(Num($(vNewDateLOI)),0)

Even better would be to analyze why the statement


LET vNewDateLOI = Num(Peek('NewDateLOI', 0, 'Projectflow32018'));

and why it returns a NULL and handle it immediately.

Anonymous
Not applicable
Author

Thanks a lot Petter ,

Sorry to say but it still does not work .

Is there any other solution you could recommend please.

Thanks,

Ravi Shinge

marcus_sommer

I think Petter is right and that the failure is caused from the wrong way to create and to call the variables. And there are some more and not only vNewDateLOI.

The easiest way for checking would be to test your routine partly by commenting the other parts. I would just start with the first load only and check if variable within the where-clause worked - if they contained a string-value the variable would need to be wrapped with single-quotes like:

where "Id Project" = '$(vIdProject2018)'

After this I would check the further variables by adding them to trace-statements to see how they are evaluated.

Similar to the first mentioned variable-call are these statements:

$(vIdProject2018) as "Id Project",

$(vDate2018) as Projectdatesflow32018,

- Marcus

Anonymous
Not applicable
Author

Hello thanks for ur solutions !

I am still not able to get solution.

But I guess I am getting null value here

NumberofFlow3hours*Projectspermonth as "Hours2018"  

Is it the right way to multiply two fields like above ...

both the fields has numerical values ..?


Kindlylet me know .

thanks,

ravi



marcus_sommer

Do I understand it right that the routine runs without an error-message but the results within your target-table is different to your expectation?

In this case you could just extend your used variables/fields to the trace- and load-statements with their origin values and also something like isnum(Field) - I think it will be then quite obvious which of the values has different formats/values as expected. Further do also a check if your loop has so many iterations like you assume.

- Marcus

Anonymous
Not applicable
Author

Thank Marcus,

Now actually for this below statement the value is null . vHours2018 is null .

LET vHours2018 = Num(Peek('Hours2018', 0, 'Projectflow32018'));

So basically this is null

NumberofFlow3hours*Projectspermonth as "Hours2018"  .

So could u let me know how do I multiply NumberofFlow3hours and  Projectspermonth both contains numerical values into another field as Hours2018. Have I multiplied them right way using right syntax .

Kindly let me know .


Thanks,

ravi

marcus_sommer

I would check within a separate load what your fields NumberofFlow3hours and Projectspermonth really contain - are there really values and if are they numbers too? A check on a number could be done with:

isnum(NumberofFlow3hours)

and if the value looked like a number but isnum() returned false the values needs to be converted with num#().

- Marcus

jmartinez
Contributor
Contributor

Hi all!

I'm having some troubles with this example script from the Qlik Sense Cookbook (find attached a picture).

I can execute the script and the display says that the App has been saved correctly but as you can see the CountrySales table is highlighted in red and no data gets loaded.

I know this is pretty basic but I need to understand the reason of this and how to load multiple files contained in one folder to create a single table.

Thanks in advance.