Set Analysis Expression for SQL query

[rohitraturi]

SELECT count(c.chgno) as "Denied Claims"

FROM charge_t as c

inner join

payment_t as p

on c.chgno = p.chgno

where postingdate between '01/01/2017' and '12/31/2017' and p.payamt = 0 and p.correction = 'N' and p.msgcode1 is not null

[OmarBenSalem]

What exactly do u want to achieve? why set expression?

Do u want to load the data into qlik sense? Or is it already loaded?

[rohitraturi]

Data is already loaded in Qliksense. I want to filter the data with the condition that is there in the where clause of SQL query

[vamsee · Accepted solution]

I would suggest you to use 2 calendar objects to empower the user to select dates and carry forward those dates in variables to expressions instead of using Hardcoded values.

COUNT({<postingdate ={">$(=vStartDate)<=$(=vEndDate)"}, p.payamt = {0} , p.correction = {'N'}, p.msgcode1={'*'} >}chgno)

Else you can replace the $(=variable) with your dates.

Note: postingdate and (variables or hardcoded date values have to be in the same format ).

[rohitraturi]

Thanks, I tried it but I am not getting the same output as of SQL.

[vamsee]

Can you please explain more?

May be try adding Posting Date as dimension and check if all the days are coming thru as expected.

[rohitraturi]

For now only focus on last three conditions. I want to count the charge numbers for which the claims are denied. A claim is denied when following three conditions are met i.e. payamt = 0 and msgcode1 is not null and correction = 'N'. I have used following code in set analysis but I get output as 0

count({<payamt={0}, correction={'N'}, msgcode1={"*"}>} chgno)

[vamsee]

Try adding 2 list boxes and see if payamt, correction have values 0 and N.

Also, * has to be in single quotes not double.

msgcode1={'*'}

[wdchristensen]

Another option is to create a flag in your sql load and then you just check to see if the flag is true in your set analysis. This is particularly useful if Qlik is not the only destination for the data (crystal, srrs, etc).

SELECT count(c.chgno) as "Denied Claims",

IIF(postingdate between '01/01/2017' and '12/31/2017' and p.payamt = 0 and p.correction = 'N' and p.msgcode1 is not null, 'TRUE', 'FALSE') as FilterFlag

FROM charge_t as c

inner join payment_t as p on c.chgno = p.chgno

group by IIF(postingdate between '01/01/2017' and '12/31/2017' and p.payamt = 0 and p.correction = 'N' and p.msgcode1 is not null, 'TRUE', 'FALSE')

[rohitraturi]

I got the required output. Its kind of silly but I just used 0.00 instead of 0 in payamt. Thank you guys for answering.