Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

hopkinsc
Valued Contributor

Adding a field from a table to another table

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?

7 Replies
Not applicable

Adding a field from a table to another table

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

hopkinsc
Valued Contributor

Adding a field from a table to another table

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

Not applicable

Adding a field from a table to another table

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.

RR

Note:

My code snippet isn't syntax checked.

Not applicable

Adding a field from a table to another table

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

Highlighted
hopkinsc
Valued Contributor

Adding a field from a table to another table

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

Sample.JPG

Any thoughts?

hopkinsc
Valued Contributor

Adding a field from a table to another table

Any more ideas on this please?

Not applicable

Adding a field from a table to another table

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