Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey everyone,
Here is my code :
HR_EMPLOYEES:
mapping load
ATTRIBUTE5,
EMPLOYEE_ID
from HR_EMPLOYEES.qvd(qvd);
test:
LOAD
RESOURCENAME as ATTRIBUTE5,
Applymap('HR_EMPLOYEES', RESOURCENAME) as employee_id,
EVENTCODEID,
EVENTDATETIME as Event_shift_time
from OtherTable.qvd (qvd);
In the output of table "test", the "employee_id" field is supposed to contain the values of EMPLOYEE_ID (in HR_EMPLOYEES), but it doesn't, the field employee_id contains the same values as ATTRIBUTE5.
I really don't get why 😕
Any idea?
thank you for your help
Laura
please check if the values of ATTRIBUTE5 from HR_EMPLOYEES and RESOURCENAME from othertable
are identical. the behaviour described would result if no match is found. maybe different upper/lower case?
if number different number format??
Since you didnt specify a default value in your apply map statement, it is just returning RESOURCENAME from the test table.
It doesnt look like it is finding any matches between ATTRIBUTE5 and RESOURCENAME. Are they in the same format?
Hi Laura, ApplyMap() keeps the value of the ValueToSearch if this doesn't exist in the Map table, I would load the values of HR_EMPLOYEES in an isolated table (unique field names, ie adding MAP_ prefix to the fields name) and check if there is the same value for MAP_ATTRIBUTE5 and ATTRIBUTE5 (no blank spaces or anything different).
Hi Laura,
The technique used for ApplyMap seems to be correct.
Just check the values in 'ATTRIBUTE5' matches with 'RESOURCENAME'.
Also, give the thhird parameter of the ApplyMap with a default vale.
Hope it helps!
Cheers !
Try the statement below and if you are getting Invalid for each field the match isn't being met. If that is the case can you provide a table box of two fields to see why the match isn't being made?
HR_EMPLOYEES:
MAPPING LOAD
EMPLOYEE_ID,
ATTRIBUTE5
FROM HR_EMPLOYEES.qvd(qvd);
TEST:
LOAD
RESOURCENAME as ATTRIBUTE5,
APPLYMAP('HR_EMPLOYEES', RESOURCENAME,'Invalid') AS EMPLOYEE_ID,
EVENTCODEID,
EVENTDATETIME as Event_shift_time
from OtherTable.qvd (qvd);
Or temprarily remove the MAPPING prefix from your code. You'll get an additional table that uses ATTRIBUTE5 as a key to connects EMPLOYEE_ID to the test table.
Use a Table box to check wether every EVENTCODEID has an ATTRIBUTE5 value and a corresponding EMPLOYEE_ID.
you are right, I've put a default value in the applymap and it always returns this value.
That's very weird because I 've tried :
HR_EMPLOYEES:
load
ATTRIBUTE5,
EMPLOYEE_ID
from HR_EMPLOYEES.qvd(qvd);
test:
LOAD
RESOURCENAME as ATTRIBUTE5,
EVENTCODEID,
EVENTDATETIME as Event_shift_time
from OtherTable.qvd (qvd);
So I do get 2 tables linked on ATTRIBUTE5 and then, I do have an EMPLOYEE_ID for each ATTRIBUTE5...
I use an old version of Qlikview (9), might this be the reason ?
Thank you again
Is EMPLOYEE_ID and ATTRIBUTE5 a 1:1 relationship. Maps only work correctly for a 1:1 relationship while just creating a link between the tables would work for 1:n relationships.
You could try doing a left join instead of a map and see if that produces values:
test:
LOAD
RESOURCENAME as ATTRIBUTE5,
EVENTCODEID,
EVENTDATETIME as Event_shift_time
from OtherTable.qvd (qvd);
Left Join (test)
load
ATTRIBUTE5,
EMPLOYEE_ID as newemployeeid
from HR_EMPLOYEES.qvd(qvd);
Thank you !
I've changed my code to :
HR_EMPLOYEES:
Mapping load distinct
ATTRIBUTE5,
EMPLOYEE_ID
from HR_EMPLOYEES.qvd(qvd)
where not isnull(ATTRIBUTE5) ;
And it is now working !