ApplyMap function

[shiveshsingh]

Hi

please let me know the error in below script

Score :mapping LOAD * INLINE [

    Name, Gender

    A, M

    B, F];

Gender: load *, ApplyMap('Score',Gender,'NA') as x

inline [Name,Score

A,20

b,50] where x='M';

[hector_munoz]

Hi Singh,

Try:

Score:

mapping LOAD * INLINE [

    Name, Gender

    A, M

    B, F];

Gender:

load *

where x ='M';

load *,

     ApplyMap('Score',Name,'NA') as x;

LOAD * inline

[Name,Score

A,20

b,50];

Take into account that QlikView is case sensitive... Hope it serves!

Regards,
H

[MK_QSL]

Instead of Gender in ApplyMap, you have to use field which is similar to the 1st Field of Mapping Load table.

So here, you have to use Name

[shiveshsingh]

Hi

plz confirm one thing, how can you define the same x as variable and as field name

load *

where x ='M';

load *,

     ApplyMap('Score',Name,'NA') as x;

[hector_munoz]

Hi Singh,

Here you are making nested LOAD sentences starting in the bottom one until the top one, so you are playing with the fields you are creating:

Gender:

load *     // 3rd level: we have all the previous fields ('x', 'Name' and 'Score') but we filter by 'x' field

where x ='M';

load *,

     ApplyMap('Score',Name,'NA') as x; // 2nd level: We add 'x' field to previous 'Name' and 'Score'

LOAD * inline     // 1st level: 'Name' and 'Score' fields

[Name,Score

A,20

b,50];

Hope it is useful!

Regards,

H

[shiveshsingh]

Hi Hector

Could you please let me know what is the exact error in below statement?

Score :mapping LOAD * INLINE [

    Name, Gender

    A, M

    B, F];

Gender: load *, ApplyMap('Score',Name,'NA') as x

inline [Name,Score

A,20

B,50] where x='M';

[hector_munoz]

HI,

The problem is in 'Gender' table and in its nested loads. The sentences of 'Gender' table should be:

Gender:

load *

where x='M';

load *,

ApplyMap('Score',Name,'NA') as x;

LOAD * inline

[Name,Score

A,20

B,50];

1st LOAD:

LOAD * inline

[Name,Score

A,20

B,50];

Creation of Gender table from an inline table with 'Name' and 'Score' fields.

2nd LOAD:

load *,

ApplyMap('Score',Name,'NA') as x;

B,50];

Creation of 'x' field from an ApplyMap.

3rd LOAD:

load *

where x='M';

Filtering records with 'x'= "M". This step has to be in this LOAD as in the preceding one we created X field.

Jope it is useful!

Regards,

H