Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, in my load script i am creating a new field using wildmatch...
SET cFileName = $(vcRetailQVD)StoreStockMovement.qvd;
IF QvdCreateTime('$(cFileName)') >= 0 THEN
StoreStockMovement:
concatenate LOAD
StoreNumber,
StoreDate,
[Stock Movement Reason Code],
EANCode AS EAN,
Narrative,
StockQty,
StockValue,
LinesAffected,
RSPValueDiff,
CostValueDiff,
StoreTime,
%LkStoreReasonCode,
if(wildmatch(Narrative,'Goods Rec*')>0,'Goods Received/Returned',
if(wildmatch(Narrative,'Stock*')>0,'Stock Checking Variances')) AS [Stock Reason1]
FROM $(cFileName) (qvd);
and in another table i am renaming a field :
SET cFileName = $(vcRetailApplicationResource)StoreReasonCode.csv;
IF FileTime('$(cFileName)') >= 0 THEN
StoreReasonCode:
LOAD
@2 AS [Stock Movement Reason Code],
@3 AS ReasonDescription,
@5 AS [Stock Reason],
@3 & ' - ' & @2 AS [Stock Adj Reason]
FROM [$(vcRetailApplicationResource)StoreReasonCode.csv]
(txt, codepage is 1252, no labels, delimiter is ',', msq);
EndIf
What i want to do is combine the 2 fields into one
Stock Reason1
Stock Reason
Can anyone help me with this please?
Thanks
Chris
Hi,
First of all, you are getting that error because I was loading from a table that doesn't exist. So you have to change this line in my code above
RESIDENT StoreStockMovement; // I take the code and reason from the table above
to this line instead
RESIDENT ProductSale; // I take the code and reason from the table above
that is the table storing the concatenated records.
Try now and let me know if that works and returns what you expect. If not, please post what you are currently getting and what do you want to get.
Hope that helps.
BI Consultant
Thanks again. That gave me a field(Stock Reason) with just 'No Reason ' . and a field Stock Reason1 with 'Goods Received/Returned, and Stock Checking Variances)
See attached...
Why is there a reference to [Stock Movement Reason Code]?
ReasonCodeNameMap:
MAPPING LOAD [Stock Movement Reason Code],
[Stock Reason1]
RESIDENT ProductSale; // I take the code and reason from the table above
I don't understnad why this field is being used? The fields i need are Stock Reason and Stock Reason1..
Or am i missunderstanding something?
I have attached my full app, (12mb though)
Where i am getting 'No Reason' in one field, and 'Goods Received/Returned', and 'Stock Checking Variances' in another field, i would want to get a field which would have
We are not loading in :
//@5 AS [Stock Reason], // don't want this field
from the StoreReasonCode table
But this field is needed. i just want the field created by using the wildmatch function in the StoreStockMovement table (ProductSale after the concatenation) added to it.
[Stock Reason] (from StoreReasonCode) + [Stock Reason1] (from ProductSale) = Combined table
Hi,
Then modify the code so
StoreReasonCode:
LOAD
@2 AS [Stock Movement Reason Code], // See comment in table StoreStockMovement
@3 AS ReasonDescription,
// next line is a new field concatenating from both tables with a blank space
@5 & ' ' & ApplyMap('ReasonCodeNameMap', @2, 'No Reason') AS [Stock Reason], // you want this instead
@3 & ' - ' & @2 AS [Stock Adj Reason]
FROM [$(vcRetailApplicationResource)StoreReasonCode.csv]
(txt, codepage is 1252, no labels, delimiter is ',', msq);
If "Stock Movement Reason Code" is not the field to use, you still need some key field in both tables so the proper Reason is passed from the first table to the second.
Hope that helps.
BI Consultant
Hi Miguel, That still didn't do the job... I have decided that what i'm trying to do may be a bit too difficult, so instead i am going to use the WILDMATCH function for ALL reasons instead of just the 2 that i had earlier.
This should work for me but it is just a bit more messy as i need to use the Narrative field (which is basically a system generated reason for each stock movement). the problem it will cause is that some reasons have the same Narrative description. i.e.
Refrigerator Breakdowns (which is code 36)
and
Waste - Standard (which id code 3)
will both have a Narrative of 'Wastage'
I can use the [Stock Movement Reason Code] field to seperate the figures, it just won't be as user friendly.
Well thanks you so much for your help. I really do appreciate it..
Thanks
Chris