Hi,
Mapping tables are used in combination with the ApplyMap function. A mapping table loads always and only two fields, the first of which is used as a parameter in the ApplyMap function. When the ApplyMap function is called, the first parameter is the mapping table to look into, the second parameter is the field you want to search for and the third is a fixed expression by default, when no coincidences are between both tables.
In my example above, I'm using "@2" because I'm using your data to load, and "@2" is the field that stores the ReasonCode in the file.
Said in different words, what I want is to say QlikView "give me the correspondence in the mapping table for the current value of the field @2, and if you don't find any value, then return the default value 'No Reason'".
But I'm afraid I may be missing something here, what do you mean by "I want to add the 2 BOLD fields together, (StockReason1 and [Stock Reason])"?
- Is the field @2 in your table "StoreReasonCode" (renamed as "Stock Movement Reason Code" in your script above) the same as "Stock Movement Reason Code" in the table "StoreStockMovement"?
- If yes, is the field "Stock Reason1" the one you want to use in the table "StockReasonCode" instead of @5 (renamed as "Stock Reason")?
If yes, you need a mapping table from the "StoreStockMovement" table having the Code and the Reason, and use the ApplyMap in the "StoreStockCode" table instead of the actual field @5 that seems to have the Reason descriptive name in that table. Is this correct?
The code should then look like the following
SET cFileName = $(vcRetailQVD)StoreStockMovement.qvd;
IF QvdCreateTime('$(cFileName)') >= 0 THEN
StoreStockMovement:
concatenate LOAD // this CONCATENATE means that there is a table above this, is that right?
// If not, you cannot concatenate
StoreNumber,
StoreDate,
[Stock Movement Reason Code], // This is the field @2 in the StoreReasonCode table
EANCode AS EAN,
Narrative,
StockQty,
StockValue,
LinesAffected,
RSPValueDiff,
CostValueDiff,
StoreTime,
%LkStoreReasonCode,
// These values are to be set in the StoreReasonCode table
if(wildmatch(Narrative,'Goods Rec*')>0,'Goods Received/Returned',
if(wildmatch(Narrative,'Stock*')>0,'Stock Checking Variances')) AS [Stock Reason1]
FROM $(cFileName) (qvd);
ENDIF
// Here we go
ReasonCodeNameMap:
MAPPING LOAD [Stock Movement Reason Code],
[Stock Reason1]
RESIDENT StoreStockMovement; // I take the code and reason from the table above
SET cFileName = $(vcRetailApplicationResource)StoreReasonCode.csv;
IF FileTime('$(cFileName)') >= 0 THEN
StoreReasonCode:
LOAD
@2 AS [Stock Movement Reason Code], // See comment in table StoreStockMovement
@3 AS ReasonDescription,
//@5 AS [Stock Reason], // don't want this field
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);
EndIf
Now you have the reason from table StoreStockMovement in table StoreReasonCode.
Hope that helps
Miguel Angel Baeyens
BI Consultant
Comex Grupo Ibérica
.png "Miguel_Angel_Baeyens"){kind=avatar}
