4 Replies Latest reply: Jul 17, 2012 9:47 AM by frank crezee RSS

    Variable in load statement

    Hamish Imrie

      Hi,

       

      I am loading rows and based on the valie of a field in a row I want to change the value of a variable.

       

      I am currently trying:

       

       

      MTDCalcsViewtoCalc:

      LOAD

      COB_Date,

      Month,

      Drawdown,

      IF(Month = Previous(Month), '$(vMTDTotalsVariable)' = '$(vMTDTotalsVariable)' + [Sector Daily PnL],'$(vMTDTotalsVariable)' = '$(vMTDTotalsVariable)'),

      '$(vMTDTotalsVariable)' as [MTD Drawdown]

      Resident MTDCalcsView;

       

      Clearly this wont work as the IF Statement is not working on the variable directly.

      How do I create this if statement to make the variable add from the previous row?

       

      H

        • Re: Variable in load statement

          Hey H,

          If I understood your problem correctly, you might not need the variable to change.

          What you want is, if month = previous month, [MTD Dradown] to be some value + Sector Daily, right?

           

          If so, you have to set the variable once and use something like this.

          Set Variable='somevalue';

           

          If (Month=Previous(Month), $(Variable)+[Sector Daily], $(Variable))               as  [MTD Dradown]

           

          Hope that helps.

           

          gr.

          Frank

            • Re: Variable in load statement
              Hamish Imrie

              Hi Frank

               

              Thanks for the response.

               

              You are correct with that statement but I need to enhance it.

              If (Month=Previous(Month), $(Variable)+[Sector Daily], $(Variable)) as  [MTD Dradown]

               

              I need to make it as such

               

              IF Month = Previous Month then

                 $(Variable) = $(Variable) + Current Month,

                 $(Variable))

               

              I am trying to build a counter that is increased by a value in the rows according to a condition.

               

              Regards

               

              H

                • Re: Variable in load statement
                  Hamish Imrie

                  I am now assuming it is not possible to have a "Let" or a "Set" statement in the load script between the

                  Load and From bits??

                    • Re: Variable in load statement

                      He you could try something with a loop;

                       

                      For example:

                       

                      Let vVariable=0;

                       

                       

                      let i=0;

                         

                      do until i=5;

                      TMP:

                      LOAD RowID,

                           Date,

                           Month,

                           Year,

                           Value,

                           Dim

                          

                      FROM

                      C:\test\test.qvd

                      (qvd)

                      where RowNo()=$(i);

                       

                       

                      LEt vVariable=$(vVariable)+FieldValue('Value',1);

                       

                       

                      i=i+1;

                      loop;

                       

                       

                      You still have to tweak it to your own data.

                      If you could upload an qvd I can maybe help you better.

                       

                      gr.

                      Frank