topic Re: Joining 2 fields from 2 tables in QlikView

Joining 2 fields from 2 tables

hopkinsc — Thu, 08 Sep 2011 13:56:26 GMT

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?

Joining 2 fields from 2 tables

hopkinsc — Thu, 08 Sep 2011 15:26:36 GMT

Hi all, anyone have any ideas on this?

Re: Joining 2 fields from 2 tables

Thu, 08 Sep 2011 15:40:00 GMT

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.

Re: Joining 2 fields from 2 tables

hopkinsc — Thu, 08 Sep 2011 15:53:58 GMT

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?

Joining 2 fields from 2 tables

Anonymous — Fri, 16 Sep 2011 13:54:59 GMT

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?