Issue with "Where" function

[robin_heijt]

Hi,

In my data load script I am currently using a where statement to filter out unnecessary strings.

The code looks as follows:

1st script, to allow for the where statement to be found:

Load*,
 If("Action type"='Organization Reassignment' and "Action Reason"='Promotion within band' or "Action Reason"='Promotion - Band Up' or "Action Reason"='Lateral Move', 'In Scope',
 if("Action type"='Termination-Voluntary' or "Action type"='Termination-Involuntary','In Scope', 'Out of Scope' ))as "Movement Exclusion";


Load
"Personal ID",
     "Name Movement",
"Employee Group Movement",
"Date of the Movement",
     "Action type",
     "Action Reason",
     "Band Before",
"Position ID Before",
"Position Before",
"Function Before",
"Band After",
"Position ID After",
"Position After",
"Function After",
"In scope for Turnover?",
"TO Filter"
     Resident Action_Report;
     Drop table Action_Report;

2nd script that utilizes the where statement:

 Movement:
Load
"Personal ID",
     "Name Movement",
"Employee Group Movement",
"Date of the Movement",
     "Action type",
     "Action Reason",
     "Band Before",
"Position ID Before",
"Position Before",
"Function Before",
"Band After",
"Position ID After",
"Position After",
"Function After",
"Movement Exclusion",
"TO Filter"
     Resident Movements
     where "Movement Exclusion"='In Scope' and "TO Filter"='In Scope';
     Drop table Movements;

 Outer Join(Employee_Report)

This an example of the source data

ID	Name	Action Type	Action Reason
1002	John Doe	Organization Reassignment	Promotion - Band Up
1002	John Doe	Medical Leave	Long-Term Disability
1122	Jack Johnson	Hiring-Employees	New Position

As per the "Movement Exception" rule applied through the "Where" function, only the first line will be shown in Qlik Sense, where as the last 2 lines are not shown due to the "where" function.

This all works perfect, however the count function still seems to recognize the 2 excluded lines. So when I create a table I will only see one line (The top one), but when I add in a KPI for count(ID), or add the count function into a bar chart or anywhere else, I still get 3 ID's as a count. Even though they are excluded through the "Where" function.

How do I fix this issue? Anyone any ideas?

Cheers.

[avkeep01]

Have you tried to use COUNT(DISTINCT ID). maybe you've got the same rows multiple times in your dataset. So you'll see 1 row, but basically there are more rows.

[robin_heijt]

Sorry I forgot to mention, Count distinct indeed works correct. But I would have to apply distinct within every graph and KPI, I would somehow like to add this distinct function within the data load script. Do you have any ideas?

Cheers

[Lauri]

Try adding Distinct to the load statement ("Movement: Load Distinct... Resident Movements").

Or does your load, into the "Movement" table, have so much detail in other fields that cause multiple rows per ID despite a "Load Distinct"?

If you can't avoid loading those fields, you'll have to use the Count(Distinct ID) everywhere. (You can make life easier in the long term by creating a variable or master measure.)

[avkeep01]

You'll need to check your script to figure out why there are multiple rows. Maybe a join or something.