topic Making a POSITIVE value a MINUS in QlikView

Making a POSITIVE value a MINUS

hopkinsc — Wed, 04 Apr 2012 12:24:22 GMT

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

Making a POSITIVE value a MINUS

Anonymous — Wed, 04 Apr 2012 12:54:38 GMT

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

hopkinsc — Wed, 04 Apr 2012 13:02:44 GMT

Hi Dennis,

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

Making a POSITIVE value a MINUS

hopkinsc — Wed, 04 Apr 2012 13:04:08 GMT

hi Dennis,

Where in my load would this go please?

Thanks

Chris

Making a POSITIVE value a MINUS

Anonymous — Wed, 04 Apr 2012 13:14:51 GMT

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 — Wed, 04 Apr 2012 13:16:22 GMT

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

Anonymous — Wed, 04 Apr 2012 13:21:03 GMT

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

hopkinsc — Wed, 04 Apr 2012 13:46:50 GMT

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

Making a POSITIVE value a MINUS

Anonymous — Wed, 04 Apr 2012 13:52:29 GMT

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.