Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
mattphillip
Creator II
Creator II

WHERE function with multiple values

Hoping someone can help. I've included a WHERE clause I'm trying to add to our ETL staging app for one of our student number reporting apps. Code is included below. Basically I'm trying to exclude students with a LVL_DETAILED value of 'F-FIFER' from the ACAD_SESSION between 2017/18-2022/23 (I'd like to retain all other students in those years), BUT I'd also like to exclude students with a SCHOOL value of AbLE and S5 across all years.

WHERE ([LVL_DETAILED]='F-FIFER' and match(ACAD_SESSION,'2017/18','2018/19','2019/20','2021/22','2022/23'))
or not match(SCHOOL,'AbLE','S5');

Currently the code excludes all students from with an ACAD_SESSION between 2017/18-2022/23.

Any help would be most appreciated.

Thank you.

Matt

Labels (1)
5 Replies
Anil_Babu_Samineni

@mattphillip I would prefer like below for last condition.

AND match(SCHOOL,'AbLE','S5'); // Note, Here I removed "OR" and "Not"

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
qv_testing
Specialist II
Specialist II

may be this..

WHERE [LVL_DETAILED]='F-FIFER' and (match(ACAD_SESSION,'2017/18','2018/19','2019/20','2021/22','2022/23')
and not match(SCHOOL,'AbLE','S5'));

mattphillip
Creator II
Creator II
Author

Hi Anil,

Thanks for the suggestion. Unfortunately that didn't load any records to the front end. I realise my script quoted is wrong as I'm trying to exclude these records not include them. It should read:

 

WHERE (not match([LVL_DETAILED]='F-FIFER') and not match(ACAD_SESSION,'2017/18','2018/19','2019/20','2021/22','2022/23'))
or not match(SCHOOL,'AbLE','S5');

But this script filters out all the years between 2017/18 and 2022/23 for all students and not just for those students with a LVL_DETAILED value of 'F-FIFER'.

mattphillip
Creator II
Creator II
Author

Thanks for the reply. Unfortunately that didn't work either. My current attempt is the below:

WHERE (not match([LVL_DETAILED],'F-FIFER') and not match(ACAD_SESSION,'2017/18','2018/19','2019/20','2021/22','2022/23'))
or not match(SCHOOL,'AbLE','S5');

However, I still have the problem that its excluding those five years for all students.

marcus_sommer

It's not really clear for me what do you want to filter but chaining multiple conditions with extra NOT statements is rather not expedient - probably possible but more complex as sensible. Simpler are often approaches like:

rangesum(
sign(match([LVL_DETAILED],'F-FIFER')), sign(match(ACAD_SESSION,'2017/18','2018/19','2019/20','2021/22','2022/23')),
sign(match(SCHOOL,'AbLE','S5'))) >= CheckValue

or maybe

(sign(match([LVL_DETAILED],'F-FIFER')) * sign(match(ACAD_SESSION,'2017/18','2018/19','2019/20','2021/22','2022/23'))) + 
sign(match(SCHOOL,'AbLE','S5'))