Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
alwayslearning
Creator
Creator

Mapping and use the new names in the Load Statements

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

  

DepartmentActiveScore
BiologyYes90
ChemistryYes80
Bio ChemistryNo
PhysicsYes
EngineeringYes50
ArtYes40
GraphicsNo50
CeramicsNo50

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,

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

6 Replies
Clever_Anjos
Employee
Employee

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

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
Creator
Creator
Author

Thanks, Works Perfectly

alwayslearning
Creator
Creator
Author

Thanks for the help on this. Works great

alwayslearning
Creator
Creator
Author

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;