Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, i want to Join 2 fields from 2 different tables. I am using a WILDMATCH function to create a new field in the first table which i want to join to a field in the 2nd table.
here are my tables..
SET cFileName = $(vcRetailQVD)StoreStockMovement.qvd;
IF QvdCreateTime('$(cFileName)') >= 0 THEN
StoreStockMovement:
left join LOAD
StoreNumber,
StoreDate,
[Stock Movement Reason Code],
EANCode AS EAN,
Narrative,
StockQty,
StockValue,
LinesAffected,
RSPValueDiff,
CostValueDiff,
StoreTime,
if(wildmatch(Narrative,'Goods Rec*')>0,'Goods Received/Returned',
if(wildmatch(Narrative,'Stock Che*')>0,'Stock Checking Variances')) AS [Stock Reason1]
FROM $(cFileName) (qvd);
ENDIF
SET cFileName = $(vcRetailApplicationResource)StoreReasonCode.csv;
IF FileTime('$(cFileName)') >= 0 THEN
StoreReasonCode:
Join LOAD
@2 AS [Stock Movement Reason Code],
@3 AS ReasonDescription,
@5 AS [Stock Reason2],
@3 & ' - ' & @2 AS [Stock Adj Reason1]
FROM [$(vcRetailApplicationResource)StoreReasonCode.csv]
(txt, codepage is 1252, no labels, delimiter is ',', msq);
I want to join Stock Reason1 from the first table and Stock Reason 2 from the 2nd table and call it Stock Reason. so the result will be a field named...
Stock Reason which will contain the wildmatch results from the first table and the results from the field in the 2nd table.
Can anyone help please?
Hi all, anyone have any ideas on this?
From what I see, [Stock Reason2] will equal either 'Goods Received/Returned' or 'Stock Checking Variances' right?
If those are the only values of [Stock Reason2] then you could just rename @5 as [Stock Reason1] for the link. If they do not, and you do not want the link, but just want to combine the contents, then I believe it would use concatenate of some sort which I am not familiar with.
Hi Marc, no, Stock Reason 2 (which is in the 2nd table) contains 7 stock reasons. Stock Reason1 is the one that contains 'Goods Received/Returned' or 'Stock Checking Variances'.
Yres i want to combine the contents of them both.
Does anyone have any ideas on how i can achieve this please?
Try adding something like this to your script:
Join (StoreStockMovement)
Load [Stock Reason2] as [Stock Reason1]
Resident StoreReasonCode
;
This should imo load your [Stock Reason2] from StoreReasonCode and join it with [Stock Reason1] in your StoreStockMovement table.
Is that what you are looking for?