IF Statement from different sources

[robin_heijt]

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.

[YoussefBelloum]

Hi,

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

[robin_heijt]

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

Could you explain by any chance?

[YoussefBelloum]

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]

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]

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]

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 · Accepted solution]

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]

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?