topic Re: IF Statement from different soi in App Development

IF Statement from different sources

robin_heijt — Tue, 11 Sep 2018 08:19:52 GMT

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.

Re: IF Statement from different soi

YoussefBelloum — Tue, 11 Sep 2018 08:42:25 GMT

Hi,

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

Re: IF Statement from different soi

robin_heijt — Tue, 11 Sep 2018 09:19:33 GMT

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

Could you explain by any chance?

Re: IF Statement from different soi

YoussefBelloum — Tue, 11 Sep 2018 09:26:56 GMT

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

Re: IF Statement from different soi

robin_heijt — Tue, 11 Sep 2018 09:29:41 GMT

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.

Re: IF Statement from different sources

YoussefBelloum — Tue, 11 Sep 2018 09:36:10 GMT

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

Re: IF Statement from different sources

robin_heijt — Tue, 11 Sep 2018 09:56:16 GMT

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.

Re: IF Statement from different sources

YoussefBelloum — Tue, 11 Sep 2018 11:25:51 GMT

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

Re: IF Statement from different sources

robin_heijt — Tue, 11 Sep 2018 16:08:34 GMT

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?