Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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)
Hi Dennis,
It's actually StockValue and StockQty i want to change.
hi Dennis,
Where in my load would this go please?
Thanks
Chris
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?
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);
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
......
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
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.