topic Re: Use field from another table as key in QlikView

Use field from another table as key

Tue, 26 Aug 2014 09:29:53 GMT

Hi all.

I need to create a key with to fields but they are from two differnt tables. The key should be with Date and lWorkstationNmbr but lWorkstationNmbr are from tha table Data.xlsx. Have the following script that doesn't work, any suggestions have to solve this?

WN:

LOAD DISTINCT

TransactionID2,

DateTime,

Date,

tp5kTime,

StoreID,

p5IWorkstationNmbr,

lWorkstationNmbr&Date as TP_Key,

p5kWorkstationName,

ProductCategory,

OperatorID

FROM

(biff, no labels, table is Sheet1$);

Right Join(WN)

LOAD lWorkstationNmbr,

p5WorkstationNmbr

FROM

C:\Sales_Import\Data.xlsx

(ooxml, embedded labels, table is SALES);

Re: Use field from another table as key

Thu, 28 Aug 2014 13:48:45 GMT

Hi guys,is there someone that have any solution for this? I would like to merge two fields, lWorkstationNmbr (from table Data) with field Date (from table WN) and create a key that i need to use for connecting with other tables.

Thanks in advance!

Re: Use field from another table as key

Anonymous — Thu, 28 Aug 2014 13:54:21 GMT

the right join is searching for IWorkstationNmbr, wile in the loaded table WN, there is NO IWorkstationNmbr (only added wirg date to TP_KEY, but IWorkstationNmbr does not exist) nor p5WorkstationNmbr. So how should the jin work? No results! Or is there a misspelling p5WorkstationNmbr and p5IWorkstationNmbr from WN should be equal?

Or am I missing something?

Re: Use field from another table as key

Thu, 28 Aug 2014 14:16:40 GMT

There is no misspelling and p5WorkstationNmbr should be equals. The join between WN and Data is not necessary (could use p5WorkstationNmbr as key) but I need to create a key with lWorkstationNmbr and Date.

Re: Use field from another table as key

Anonymous — Thu, 28 Aug 2014 14:20:40 GMT

it is okay if you create the key TP_Key, but for the join you Need to add the field IWorkstationNmbr as well.

WN:

Load distinct

...

IWorkstationNmbr,

IWorkstationNmbr & Date as TP_Key,

...

Then the right join should work, as there are two common fields for the join!

Re: Use field from another table as key

DavidFoster1 — Thu, 28 Aug 2014 14:24:45 GMT

Try this APPLYMAP approach:

P5WorkStations:

MAPPING LOAD

p5WorkstationNmbr,

lWorkstationNmbr

FROM

C:\Sales_Import\Data.xlsx

(ooxml, embedded labels, table is SALES);

WN:

LOAD DISTINCT

TransactionID2,

DateTime,

Date,

tp5kTime,

StoreID,

p5IWorkstationNmbr,

APPLYMAP('P5WorkStations', p5IWorkstationNmbr, 'NoWorkStation') & Date as TP_Key,

p5kWorkstationName,

ProductCategory,

OperatorID

FROM

(biff, no labels, table is Sheet1$);

Re: Use field from another table as key

MarcoWedel — Thu, 28 Aug 2014 14:25:36 GMT

please post sample data in the format of both Excel-files.

thanks

regards

Marco

Re: Use field from another table as key

Thu, 28 Aug 2014 14:47:42 GMT

The key should use lWorkstationNmbr not p5IWorkstationNmbr.

Re: Use field from another table as key

Thu, 28 Aug 2014 14:57:36 GMT

The join is working good but the problem is that the field IWorkstationNmbr is from the table Data and it is not working to create the key.

Re: Use field from another table as key

DavidFoster1 — Thu, 28 Aug 2014 16:02:08 GMT

The APPLYMAP returns IWorkStationNmbr based on a lookup of the P5IWorkStationNmbr.

Re: Use field from another table as key

DavidFoster1 — Thu, 28 Aug 2014 16:05:50 GMT

Also you need to put your SQL thinking aside. Load scripts are sequential loading processes not queries. In your script the process reads as

1) Load WN*.XLS (dataset a)

2) Load Data.XLS (dataset b)

3) RIGHT JOIN dataset a and b

If you wanted to work with what you have you could have loaded into a temp table WN_tmp and then done a RESIDENT load into a final table with the final Key.