4 Replies Latest reply: Sep 16, 2011 9:54 AM by Dennis Hoogenboom RSS

    Joining 2 fields from 2 tables

    Chris Hopkins

      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?