Use field from another table as key

[#1]

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

[#2]

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!

[Anonymous]

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?

[#4]

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.  

[Anonymous]

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!

[DavidFoster1 · Accepted solution]

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

[MarcoWedel]

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

thanks

regards

Marco

[#8]

The key should use lWorkstationNmbr not p5IWorkstationNmbr.

[#9]

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.

[DavidFoster1]

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