Help with load script

[hopkinsc]

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

[Miguel_Angel_Baeyens]

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.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[hopkinsc]

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)

[hopkinsc]

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

[Miguel_Angel_Baeyens · Accepted solution]

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.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[hopkinsc]

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