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, if(wildmatch(Narrative,'Goods Rec*')>0,'Goods Received/Returned', if(wildmatch(Narrative,'Stock*')>0,'Stock Checking Variances')) AS [Stock Reason]
FROM $(cFileName) (qvd);
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
I want to add the field created by the IF statements in the first table to the bottom table but i havnt got a clue how to do it.
If i reload it as it is then i get a syn table and the data from the field 'Stock Reason' in the 2nd table is not available.
no, that doesn't matter. Once you load external data into a QV-table, the data has no technical relationship to its source.
If your script above works correctly (and the needed files exist) you should have (at least) two QV-internal tables:
Once loaded both tables I want you to do a resident load from the (as still mentioned: internal) table "StoreStockMovement" to merge [Stock Reason] to "StoreReasonCode" and rename it to "StockReasonNew" to avoid cyclic links.
the reasons why you are getting a sync table are the identical fieldnames in "StoreReasonCode" and "ProductSafe". Now there are two possibilities:
- Join the two tables via a QV-Join (as you did with "StoreStockMovement" and "ProductSafe" before)
- or keep the two tables and link them together
In both cases you need (one or more) matching keyfields. If you got one keyfield, rename the others i.e. into "StockReason_SRC" and "StockReason_PS". If your key is a combination of several fields then you can use a function like AutoNumber() to create a new surrogato key. For ex:
AutoNumber(StockReason & StockMovementreasonCode, 1) as StockReason_ID