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:
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.
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,
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.
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 .
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:
and if the value looked like a number but isnum() returned false the values needs to be converted with num#().