7 Replies Latest reply: Jan 15, 2018 2:48 AM by Marcus Sommer RSS

    For Each statement Error

    RAVI SHINGE

      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

        • Re: For Each statement Error
          Petter Skjolden

          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.

          • Re: For Each statement Error
            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

              • Re: For Each statement Error
                RAVI SHINGE

                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



                  • Re: For Each statement Error
                    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

                      • Re: For Each statement Error
                        RAVI SHINGE

                        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

                          • Re: For Each statement Error
                            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