Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Nested Where Clause in Load Script-QlikSense

Hi all ,

I have following code. Trying to filter a subset of values using nested where clause. What would be appropriate logic because I am getting error in script this way in second part of where cloause as "   ) expected. thanks

HHMPM:

Load *

FROM [lib://ProgramQVD/HHMJOINPM.csv]

(txt, utf8, embedded labels, delimiter is ',', msq)

where (ManagerID ='' and Qtr='Q3' and Year = '2016/17')

= (Load * FROM [lib://ProgramQVD/HHMJOINPM.csv]

(txt, utf8, embedded labels, delimiter is ',', msq)

where ManagerID <>'' and Qtr='Q4' and Year = '2016/17');

5 Replies
dan_sullivan
Contributor II

Re: Nested Where Clause in Load Script-QlikSense

Try just using a join in the script:

HHMPM:

Load *

FROM [lib://ProgramQVD/HHMJOINPM.csv]

(txt, utf8, embedded labels, delimiter is ',', msq)

where ManagerID ='' and Qtr='Q3' and Year = '2016/17';

JOIN

Load * FROM [lib://ProgramQVD/HHMJOINPM.csv]

(txt, utf8, embedded labels, delimiter is ',', msq)

where ManagerID <>'' and Qtr='Q4' and Year = '2016/17';

Not applicable

Re: Nested Where Clause in Load Script-QlikSense

Hi,

Actually I want to filter only those ManagerIDs fall under (  ManagerID <>'' and Qtr='Q4' and Year = '2016/17') and select those from  (Qtr='Q3' and Year = '2016/17) not every one from  (Qtr='Q3' and Year = '2016/17)'. thanks

dan_sullivan
Contributor II

Re: Nested Where Clause in Load Script-QlikSense

Okay, there might be a more elegant way but you can try this.  create a temp table, then select from the temp table and then drop the temp table.  If i misunderstood which one needs to happen first just flip flop the where clauses.

HHMPM_TEMP:

Load * FROM [lib://ProgramQVD/HHMJOINPM.csv]

(txt, utf8, embedded labels, delimiter is ',', msq)

where ManagerID <>'' and Qtr='Q4' and Year = '2016/17';

HHMPM:

Load *

RESIDENT HHMPM_TEMP

where ManagerID ='' and Qtr='Q3' and Year = '2016/17';

DROP TABLE HHMPM_TEMP

Not applicable

Re: Nested Where Clause in Load Script-QlikSense

Hi, thanks but sorry it will not gonna work. I will try to explain the problem once again. you can suggest any solution.

I have One Table having record for Employees  in different quarters and years.

Few Employees have ManagerID in Qtr 4 but not having ManagerID in Qtr3 (its mean this field is empty for those Emplyees in Q3)

I want to first filter those Employees with ManagerID in Q4(e.g emp11 emp33, emp77) . Then want to filter records for same employess (e.g emp11 emp33, emp77) from Qtr3. (Qtr3 may have many employees and not all are required only matching with filter as with ManagerID in Q4)

the aim of this to review data for same employees when they have managerid in q4 vs when they didn't have in Qtr3. Hope I am able to explain the problem. thanks

simondachstr
Valued Contributor III

Re: Nested Where Clause in Load Script-QlikSense

Have a look at where exists() and where not exists()