Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
robin_heijt
Creator
Creator

IF Statement from different sources

Hi,

I have 2 files:

Movement Report:

Load *,

If("Action Reason" ='End of contract' , ('Out of Baseline'),

If("Action Reason" ='Retirement (payroll)' , ('Out of Baseline'),

If("Action Reason" ='Sizing' , ('Out of Baseline'),

If("Action Reason" ='Position Obsolete' , ('Out of Baseline'),

If("Action Reason" ='Restructuring (DNT Use)' , ('Out of Baseline'),

If("Action Reason" ='Employee Deceased' , ('Out of Baseline'),('In Baseline') ))))))  as "Scope";

Load *,

If("Action Reason" ='Lateral Move' , ('Lateral Move'),

If("Action Reason" ='Promotion - Band Up' , ('Promotion Band Up'),

If("Action Reason" ='Promotion within band' , ('Promotion Within Band'),

If("Action type" ='Termination-Involuntary' , ('Termination Involuntary'),

If("Action type" ='Termination-Voluntary' , ('Termination Voluntary'))))))  as "Movement Type";

LOAD

    PERNR as "Global ID Movement",

    Capitalize(Fname&' '&Lname) as "Full Name Movement",

    "EE Group" as "Employee Group Movement",

    "Action Begda" as "Date of the Movement",

    "Action type",

    "Action Reason",

    "Band Before",

    "Position Before" as "Position # Before",

    "Position Name Before EN" as "Position Before",

    Capitalize("Function Before"),

    "Band After",

    "Position After" as "Position # After",

    "Position Name After EN" as "Position After",

    Capitalize("Function After")

FROM [lib://***.xlsx]

(ooxml, embedded labels, table is [all_employee_movements_23-08-20]);

OPR Report:

LOAD

    "Employee Global ID" as "Global ID",

    Capitalize(Name) as "Full Name OPR",

      Keepchar("Last Published OPR Rating",'4A4B3A3B21A1B') as "OPR 2017"

FROM [lib://***.xlsx]

(ooxml, embedded labels, table is [OPR 2017]);

And I have an IF statement:

If("OPR 2017" ='1B' and "Movement Type" ='Termination Involuntary' or 'Termination Voluntary', 'Adherent'),

  If("OPR 2017" ='1A' and "Movement Type" ='Termination Involuntary' or 'Termination Voluntary' or 'Lateral M

  If("OPR 2017" ='2' and "Movement Type" ='Lateral Move' or 'Promotion Within Band', 'Adherent'),

  If("OPR 2017" ='3B' and "Movement Type" ='Lateral Move' or 'Promotion Within Band', 'Adherent'),

  If("OPR 2017" ='3A' and "Movement Type" ='Lateral Move' or 'Promotion Within Band' or 'Promotion Band Up',

  If("OPR 2017" ='4B' and "Movement Type" ='Lateral Move' or 'Promotion Within Band' or 'Promotion Band Up',

  If("OPR 2017" ='4A' and "Movement Type" ='Promotion Band Up', 'Adherent'), ('Non-Adherent') as Adherence;

As you can see the IF statement contains fields from both the sources (OPR 2017 & Movement Type).

However when I place this IF statement in either of the loads, it will not recognize the field from the other source.

What would I have to do, to have 2 different sources in an IF statement?

Thanks.

1 Solution

Accepted Solutions
YoussefBelloum
Champion
Champion

I was answering your question: how to join tables...

on Qlik you can join table but keep them separated (two physical table) at the same time, that's what you do when you join implicitely (using only homonyms, like the example above).

you need to explicitly join the table to be able to get 1 physical table (using join)

example


table1:

load

.

.

fieldA as ID

From...

JOIN //YOU SHOULD VERIFY THE JOIN TYPE YOU NEED, LEFT, RIGHT...

table2:

load

.

.

fieldB as ID

From...

at the end of the script, you will have one table called table1 containing all the fields from table1 and table2

View solution in original post

8 Replies
YoussefBelloum
Champion
Champion

Hi,

did you try to join both table into a single table ?

robin_heijt
Creator
Creator
Author

I tried working with the join function, however I cannot seem to get that working.

Could you explain by any chance?

YoussefBelloum
Champion
Champion

like you already mentioned, you cannot make a test (IF) on two fields (inside one load), coming from different loads (tables).

the solution is to join those table to be able to make some tests with all the fields from table A and table B.

your table are connected by a joining field(s) ?

robin_heijt
Creator
Creator
Author

Currently the only existing connection in my files are field connections by Global ID Connected to the full headcount report which I have not mentioned in my original post.

Could you explain to me how I join the tables?

Thanks.

YoussefBelloum
Champion
Champion

EDITED:

ok, but let's talk about the tables you're using to make your test work (Movement Report & OPR Report).


you need to have a joining field between those tables..


to answer your question, to join two tables using Qlik, you should rename each joining field with the same name.


example


table1:

load

.

.

fieldA as ID

From...

table2:

load

.

.

fieldB as ID

From...

==> both table here will be connected by field ID

robin_heijt
Creator
Creator
Author

Allright, thanks. I understand now.

But where do I place my IF statement now?

As I still get the message that the field from the other table is not found.

YoussefBelloum
Champion
Champion

I was answering your question: how to join tables...

on Qlik you can join table but keep them separated (two physical table) at the same time, that's what you do when you join implicitely (using only homonyms, like the example above).

you need to explicitly join the table to be able to get 1 physical table (using join)

example


table1:

load

.

.

fieldA as ID

From...

JOIN //YOU SHOULD VERIFY THE JOIN TYPE YOU NEED, LEFT, RIGHT...

table2:

load

.

.

fieldB as ID

From...

at the end of the script, you will have one table called table1 containing all the fields from table1 and table2

robin_heijt
Creator
Creator
Author

Thank you!

I understand the join now. But wherever I place my if statement, I keep getting the message 1 field not found.

Where exactly do I place the IF statement? Before or after the load?