why Join is not working?

[hadari]

Hi -
I'm trying to add the storename to my emp_name table based on the salesman_id and date fields together but the qlik is got stocked.

the join that is not working:

EmpStore:

LOAD DISTINCT
SalesmanID & '|' & date(trim(Date)) AS EmpStoreKey,
StoreHanut
FROM [lib://ETL/Sales_final.QVD](qvd);

EmpName_new:
LOAD
*
FROM [lib://ETL/EmpName.QVD] (qvd);
LEFT JOIN (EmpName_new)
LOAD
EmpStoreKey,
StoreHanut
RESIDENT EmpStore;
drop table EmpStore;

the apply map that's work:

EmpStoreMapping:
Mapping
load distinct
SalesmanID & '|' & date(trim(Date)) as EmpStoreKey,
StoreHanut
FROM [lib://ETL/Sales_final.QVD]
(qvd);

EmpName_new:
LOAD
*,
ApplyMap('EmpStoreMapping', SalesmanID & '|' & date_num_new, '') as StoreHanut
from [lib://ETL/EmpName.QVD] (qvd);

Store EmpName_new into [lib://ETL/EmpName_new.QVD] (qvd);

Drop Table EmpName_new;

when i'm using apply mapping it's working but I want to  use join to fetch more then single value.

what I'm doing wrong?

[hadari]

I replied below..
I added the entire script..

[marcus_sommer]

From a pure lookup-value point of view a join is the same as a mapping. Means if both lookup-values have the same data-structure there would be no difference between them. Assuming this is ensured you need also to restrict the join to this lookup-value because unlike specifying the join to certain key-fields in sql Qlik will use all equally named fields which may the issue in your approach.

Beside this I suggest to use always a mapping as default because it has no risks to change the number of records, you have always an immediately result which could be directly processed without any further resident-loads and mappings could be vertically + horizontally nested. And therefore matching n values mustn't be a showstopper for a single mapping, like:

m: mapping load Lookup, F1 & '|' & F2 & '|' F3 from Source;

t: load *, subfield(applymap('m', Lookup, 'no match'), '|', 1) as F1, ...
from xyz;