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?

[Anil_Babu_Samineni]

Perhaps try like this, In this directory EmpName_new, What are the *, Hope you have matching fields from Temp table in QVD.

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)

Temp:

Noconcatenate

LOAD
EmpStoreKey,
StoreHanut
RESIDENT EmpStore;
drop table EmpStore;

[pravinboniface]

@hadari Are you missing a noconcatenate?

EmpStore:

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

EmpName_new:

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

[hadari]

it says:

Illegal combination of prefixes

[hadari]

It's still got stocked.

[joshsiddle8]

It seems like you're encountering an issue with joining the EmpStore table to your existing EmpName table based on SalesmanID and date fields. The join you've attempted doesn't seem to be working as expected, resulting in your Qlik application getting stuck.

From your provided script, it appears that you're attempting a left join between EmpStore and EmpName_new tables using EmpStoreKey as the common field. However, it seems that the join operation isn't yielding the desired outcome.

One potential solution could be to ensure that EmpStoreKey is properly generated and aligned between the two tables. Additionally, it might be helpful to check for any discrepancies in the data types or formats of the SalesmanID and date fields between the two tables.

Alternatively, you mentioned that the ApplyMap function is working for your scenario. While ApplyMap can be effective, it's understandable that you prefer to use a join for fetching multiple values.

I would suggest reviewing the data in both tables and the logic of your join operation to identify any potential errors or inconsistencies. Additionally, you might consider reaching out to your Qlik support resources or community forums for further assistance and troubleshooting.

I hope this helps, and please feel free to reach out if you have any further questions or need additional support.

[hadari]

how do I check discrepancies?
the salesmanid and date are the same format otherwise the apply map wouldn't work right?

[Anil_Babu_Samineni]

Remove Noconcatenate and just mention the table name to divide and see how it works? If not, Do you think you can share some data set or explain what is not working? 

[pravinboniface]

@hadari When you say "stocked", what do you mean?  Is it just hung and not responding?

[hadari]

yes it's loading a lot of time until I need to abort...

this is my entire script:

EmpName:
LOAD
"First Name",
"Last Name",
SalesmanID
FROM [lib://DB/EmpName.QVD] (qvd);

left join (EmpName)
LOAD
date_num_new, //join
SalesmanID,//join
Department,
Manager

resident Daily_table_for_each_employee_test;
drop table Daily_table_for_each_employee_test;

left join (EmpName)

LOAd
SalesmanID,//join

Full_Date as date_num_new,
daily_cost
FROM [lib://ETL/EmpFull.QVD] (qvd);

store EmpName into [lib://ETL/EmpName.QVD] (qvd);

drop Table EmpName;

/// here I'm trying to join the storename to the salesmanid& date

EmpStore:

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

EmpName_new:
LOAD
*,
SalesmanID& '|' &date_num_new  //I  added this because this is the join field
FROM [lib://ETL/EmpName.QVD] (qvd);
LEFT JOIN (EmpName_new)
LOAD
EmpStoreKey,
StoreHanut
RESIDENT EmpStore;
drop table EmpStore;