topic Adding a field from a table to another table in QlikView

Adding a field from a table to another table

hopkinsc — Wed, 14 Dec 2011 12:36:06 GMT

hi,

I have the following script.

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);

ENDIF

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.

Can anyone help please?

Adding a field from a table to another table

Wed, 14 Dec 2011 13:05:44 GMT

Hello Chris,

you can use the Join Load to append the field. For this you need a valid key. "[Stock Movement Reason Code]" looks like a candidate (key) for me.

With this precondition a third load may look like

Left Join(StoreReasonCode)

Load

[Stock Movement Reason Code], // key to link both tables correctly

[Stock Reason] AS StockReasonNew

Resident

StoreStockMovement;

Regards, Roland

Adding a field from a table to another table

hopkinsc — Wed, 14 Dec 2011 13:48:13 GMT

Hi Roland, thanks for your reply.

If i add that to my load scrip i get an error saying the table can't be found.

Would it have anything to do with the StoreReasonCode being a CSV?

Chris

Adding a field from a table to another table

Wed, 14 Dec 2011 14:45:39 GMT

Hi Chris,

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:

- StoreStockMovement

- StoreReasonCode

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.

Note:

My code snippet isn't syntax checked.

Adding a field from a table to another table

Wed, 14 Dec 2011 15:03:15 GMT

Hi Chris,

sorry, but I didn't recognize your Join-Load until now:

StoreStockMovement:

Join LOAD

StoreNumber,

. . .

Because of this Join LOAD you do not have a table called "StoreStockMovement" as you joined it to the prior loaded table.

Please replace the tablename after my "Resident" with the tablename you joined "StoreStockMovement" to.

OK?!

Roland

Adding a field from a table to another table

hopkinsc — Wed, 14 Dec 2011 15:08:18 GMT

Ahh, i missed that! that works now but i get a syn table.

Any thoughts?

Adding a field from a table to another table

hopkinsc — Fri, 16 Dec 2011 09:58:26 GMT

Any more ideas on this please?

Adding a field from a table to another table

Fri, 16 Dec 2011 12:20:04 GMT

Hi Chris,

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

in BOTH tables and one script.

HtH

Roland