Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Applymap not working

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

13 Replies
hrlinder
Honored Contributor

Re: Applymap not working

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

jpapador
Valued Contributor

Re: Applymap not working

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?

Re: Applymap not working

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

Not applicable

Re: Applymap not working

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 !

NickHoff
Valued Contributor

Re: Applymap not working

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

Re: Applymap not working

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.

Not applicable

Re: Applymap not working

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

jpapador
Valued Contributor

Re: Applymap not working

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

Not applicable

Re: Applymap not working

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 !

Community Browser