Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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$);
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!
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?
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.
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!
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$);
please post sample data in the format of both Excel-files.
thanks
regards
Marco
The key should use lWorkstationNmbr not p5IWorkstationNmbr.
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.
The APPLYMAP returns IWorkStationNmbr based on a lookup of the P5IWorkStationNmbr.