For Each statement Error

[Anonymous]

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

[petter]

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]

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]

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]

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 · Accepted solution]

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]

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.