Mapping and use the new names in the Load Statements

[alwayslearning]

Hi,

I have used the Mapping function to change the name of various data fields in my data.  Now I want to use the new name in some IF statements in my Load Statement.  How do I go about this.

For example

Department	Active	Score
Biology	Yes	90
Chemistry	Yes	80
Bio Chemistry	No
Physics	Yes
Engineering	Yes	50
Art	Yes	40
Graphics	No	50
Ceramics	No	50

Say I used the Mapping

MAPPING:

MAPPING LOAD * inline [

Old,New

Biology, Science

Chemistry, Science

'Bio Chemistry', Science

Physics, Science

Engineering, Science

Art, Art

Graphics, Art

Ceramic, Art

];

map [Department] using MAPPING

Now I want write an If statement that basically says if Department = Art And Active ='Yes', 1,0 within the load statment from an Excel Document.

E.g

Table1:

LOAD

Department,

Active,

Score,

IF(Department='Art' and Active='Yes', '1','0' as Active department.

FROM

[Excel.xlsx]

Ultimately I will use that IF to sum the number of active Art Departments in a expression in my dashboard.

Regards,

[Clever_Anjos]

Try this:

MAPPING:

MAPPING LOAD * inline [

Old,New

Biology, Science

Chemistry, Science

'Bio Chemistry', Science

Physics, Science

Engineering, Science

Art, Art

Graphics, Art

Ceramic, Art

];

Table1:

LOAD *,

IF(Department='Art' and Active='Yes', '1','0' as [Active department];

LOAD

applymap('MAPPING',Department) as Department,

Active,

Score

FROM

[Excel.xlsx]

[sunny_talwar · Accepted solution]

You can avoid preceding load here by doing something like this:

MAPPING:

MAPPING LOAD * inline [

Old,New

Biology, Science

Chemistry, Science

'Bio Chemistry', Science

Physics, Science

Engineering, Science

Art, Art

Graphics, Art

Ceramic, Art

];

Table:

LOAD ApplyMap('MAPPING',Department) as Department,

    Active,

    Score,

    If(ApplyMap('MAPPING',Department)='Art' and Active='Yes', 1, 0) as [Active department]

FROM

[https://community.qlik.com/thread/235732]

(html, codepage is 1252, embedded labels, table is @1);

[alwayslearning]

Thanks, Works Perfectly

[alwayslearning]

Thanks for the help on this. Works great

[alwayslearning]

Hi

I have a follow up to this I have a second table and have joined these together.  I now want to use columns from both tables in my IF statement

e.g.

MAPPING:

MAPPING LOAD * inline [

Old,New

Biology, Science

Chemistry, Science

'Bio Chemistry', Science

Physics, Science

Engineering, Science

Art, Art

Graphics, Art

Ceramic, Art

];

Table:

LOAD ApplyMap('MAPPING',Department) as Department,

   ID

     Active,

    Score,

    If(ApplyMap('MAPPING',Department)='Art' and Active='Yes', 1, 0) as [Active department] ID

FROM

[https://community.qlik.com/thread/235732]

(html, codepage is 1252, embedded labels, table is @1);

left join

Table2:

ID,

Teacher

from excel sheet.

For example, I want to be able to write a IF statement (Department=Art and Active=Yes and Teacher = 'Miss Matthews'   (Note:  I believe I have joined the tables correctly in terms of syntax, by just using the Left Join statement, between the tables?)

[sunny_talwar]

You will need to create a new table to do all your new calculations which combines the fields from the two tables

Table_Final:

LOAD *,

          If(....

Resident Table;

DROP Table Table;