8 Replies Latest reply: Apr 4, 2012 9:52 AM by Dennis Hoogenboom RSS

    Making a POSITIVE value a MINUS

    Chris Hopkins

      Hi everyone,

       

      I need help turning a positive value into a minus.

       

      I am loading in a field 'Narrative' in my load script. The data within 'Naraative' are Reason Descriptions for stock adjustments.

      Currently any 'Narrative' starting with 'GFSU' is a POSITIVE figure, but i need to make it a MINUS figure, but leaving all of the other 'Narratives' as they are.

       

      Here is my script...

       

      SET cFileName = $(vcRetailQVD)StoreStockMovement_*.qvd;

      IF FileTime('$(cFileName)') >= 0 THEN

      StoreStockMovement:

        Join LOAD 

            StoreNumber,

            StoreDate,

            [Stock Movement Reason Code],

             EANCode AS EAN,

            Narrative,

            StockQty,

            StockValue,

            LinesAffected,

            RSPValueDiff,

            CostValueDiff,

            StoreTime,

            %LkStoreReasonCode

      FROM $(cFileName) (qvd);

       

       

      Here is an example of the data..

       

       

      untitled.JPG

       

      Ignore the -30.49 total, This is just a snap shot of the table.

      I need to change the above Narrative's to show a MINUS instead of POSITIVE.

       

      Can anyone help please?

       

      Thanks

        • Making a POSITIVE value a MINUS
          Dennis Hoogenboom

          You could add this to your Load:

           

          Load ...

          If (Left(Narrative, 4) = 'GFSU' , '-1' , 1  as Calc,

          ....

           

          This way you can use the new field Calc for the value you want to change from + to -

           

          or try :

           

          Load ...

          If (Left(Narrative, 4) = 'GFSU' , StockValue *-1, StockValue, as StockValue,

          ....

           

          (assuming it is the stockvalue you want to change)

            • Making a POSITIVE value a MINUS
              Chris Hopkins

              Hi Dennis,

               

              It's actually StockValue and StockQty i want to change.

                • Making a POSITIVE value a MINUS
                  Chris Hopkins

                  hi Dennis,

                   

                  Where in my load would this go please?

                   

                  Thanks

                   

                  Chris

                    • Making a POSITIVE value a MINUS
                      Dennis Hoogenboom

                      Try something like this:

                       

                      SET cFileName = $(vcRetailQVD)StoreStockMovement_*.qvd;

                      IF FileTime('$(cFileName)') >= 0 THEN

                      StoreStockMovement:

                        Join LOAD

                            StoreNumber,

                            StoreDate,

                            [Stock Movement Reason Code],

                             EANCode AS EAN,

                            Narrative,

                      If (Left(Narrative, 4) = 'GFSU' , StockQty *-1, StockQty ) as StockQty,

                      If (Left(Narrative, 4) = 'GFSU' , StockValue *-1, StockValue) as StockValue,

                            LinesAffected,

                            RSPValueDiff,

                            CostValueDiff,

                            StoreTime,

                            %LkStoreReasonCode

                      FROM $(cFileName) (qvd);

                       

                       

                      Let me know if this works ok?

                      • Making a POSITIVE value a MINUS
                        Celambarasan Adhimulam

                        Hi,

                             Check with this

                            

                        SET cFileName = $(vcRetailQVD)StoreStockMovement_*.qvd;

                        IF FileTime('$(cFileName)') >= 0 THEN

                        StoreStockMovement:

                          Join LOAD

                              StoreNumber,

                              StoreDate,

                              [Stock Movement Reason Code],

                               EANCode AS EAN,

                              Narrative,

                             if(WildMatch(Narrative,'GFSU*'), fabs(StockQty) * -1, StockQty) as StockQty,

                              if(WildMatch(Narrative,'GFSU*'), fabs(StockValue) * -1, StockValue) as StockValue,

                              LinesAffected,

                              RSPValueDiff,

                              CostValueDiff,

                              StoreTime,

                              %LkStoreReasonCode

                        FROM $(cFileName) (qvd);

                      • Re: Making a POSITIVE value a MINUS
                        Dennis Hoogenboom

                        Hi Dennis,

                         

                        It's actually StockValue and StockQty i want to change.

                         

                         

                         

                         

                        That doesn't really make sense to me btw.

                        I assume the total value is StockQty * StockValue am I right?

                         

                        If you change them both the outcome of the calculation would be the same.

                         

                        fe     4    x    25   = 100

                               -4    x   -25   = 100

                         

                        It would make more sence that you only change one of the two

                         

                             4    x -25     = -100

                         

                         

                        ......

                          • Making a POSITIVE value a MINUS
                            Chris Hopkins

                            Hi both,

                             

                            I have tried both of your suggestions but get the same error with both.

                            untitled.JPG

                             

                            Is this because StockValue and StockQty already exist in the table?

                             

                            And to answer your question Dennis,

                            StockValue is the VALUE of the narrative in £'s

                            StockQty is the unit QTY.

                             

                            Thanks

                              • Making a POSITIVE value a MINUS
                                Dennis Hoogenboom

                                I see,

                                 

                                Yes it's because create StockValue and  StockQty twice

                                 

                                Just delete them from your script.

                                Or rename them like:

                                 

                                SET cFileName = $(vcRetailQVD)StoreStockMovement_*.qvd;

                                IF FileTime('$(cFileName)') >= 0 THEN

                                StoreStockMovement:

                                  Join LOAD

                                      StoreNumber,

                                      StoreDate,

                                      [Stock Movement Reason Code],

                                       EANCode AS EAN,

                                      Narrative,

                                If (Left(Narrative, 4) = 'GFSU' , StockQty *-1, StockQty ) as StockQty,

                                If (Left(Narrative, 4) = 'GFSU' , StockValue *-1, StockValue) as StockValue,

                                StockValue as OrgStockValue,

                                StockQty as OrgStockQty,

                                      LinesAffected,

                                      RSPValueDiff,

                                      CostValueDiff,

                                      StoreTime,

                                      %LkStoreReasonCode

                                FROM $(cFileName) (qvd);

                                 

                                That should work imo.

                                 

                                Good luck.