Making a POSITIVE value a MINUS

[hopkinsc]

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..

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

[Anonymous]

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)

[hopkinsc]

Hi Dennis,

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

[hopkinsc]

hi Dennis,

Where in my load would this go please?

Thanks

Chris

[Anonymous]

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?

[CELAMBARASAN]

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);

[Anonymous]

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

......

[hopkinsc]

Hi both,

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

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

[Anonymous · Accepted solution]

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.