Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Use field from another table as key

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

1 Solution

Accepted Solutions
DavidFoster1
Specialist
Specialist

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

View solution in original post

10 Replies
Not applicable
Author

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
Not applicable
Author

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?

Not applicable
Author

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
Not applicable
Author

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
Specialist
Specialist

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

View solution in original post

MarcoWedel

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

thanks

regards

Marco

Not applicable
Author

The key should use lWorkstationNmbr not p5IWorkstationNmbr.

Not applicable
Author

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
Specialist
Specialist

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