topic Re: using a where clause in QlikView syntax in QlikView

using a where clause in QlikView syntax

Thu, 14 Jan 2016 17:02:25 GMT

Can someone please help? I am trying to count unique ER visits per patient in a dataset. In SQL it is a simple query that does a distinct count of claims when the place of service is the ER or Urgent Care.

Select pat_id, count (distinct IMPUTED_CLAIMNO) 'ER Visits'

from [dbo].[OvarianCA_Year3_Claims]

where pos in ('20','23')

group by pat_id

It runs very quickly and accurately in sql server.

I can get the distinct count of claims in QlikView Count (DISTINCT IMPUTED_CLAIMNO) but for the life of me I have been unable to figure out how to add the where clause portion of this. I have read multiple answers and it looks like a comma followed by the syntax is needed but it also looks like there is a specific sequence of {, ( or other characters that are needed and I just do not understand or see what that is. I have spent hours on this only to see error in expression.

This is not a difficult query and I could create a separate table for Qlik but there is no reason for that. The data are all there in that table which was ported from SQL Server to Qlik.

Thanks for any guidance you can provide. Steve

Re: using a where clause in QlikView syntax

ryanridley — Thu, 14 Jan 2016 20:13:43 GMT

Hey Steve,

You can add a Where Clause to the end of the statement you just have to work with the Qlik specific syntax for it.

You could write the WHERE clause like this:

WHERE pos > 19 AND pos < 24

Re: using a where clause in QlikView syntax

Thu, 14 Jan 2016 22:41:27 GMT

Thank you!

Re: using a where clause in QlikView syntax

Sat, 16 Jan 2016 12:10:24 GMT

Thanks again for responding. Unfortunately this did not work. Even if I try to filter the result by adding pos=20 (or pos='20' I still get no results though it calculates fine without the filter. I still think this looks to be a syntax issue of some sort and I am just not getting it. Have actually combed through the community responses about analysis sets, filters, where clauses and read a slew of responses and examples but I am still not getting it and I have not found a good source that outlines the syntax that QlikView expects.

I will keep searching and post here if I find it. Any and all other suggestions welcome.

Steve

Re: using a where clause in QlikView syntax

Sat, 16 Jan 2016 14:35:48 GMT

Well I think I was able to figure this out on my own... with the help of reading a lot of other folks questions and answers.

I was trying to show the unique patients, costs, and number of unique ER visits in a dataset where ER and Urgent Care are identified by a point of service code of 20 or 23. You cannot use a range because there are values between 20 and 23 that do not correspond to the correct pos code but I could not get the range to work anyway.

What I came up with is probably not elegant but it works.

1. I created a calculated dimension

=If(Service_Category= 'ER/Urgent Care', [Service_Category])

2. Counting patients was easy

Count (DISTINCT pat_id)

3. I changed the name of the allowed $ field in a subtable to be able to call it directly

Sum (Macroeconomic_Allowed$)

4. I finally found an answer using inclusions and exclusions that did the trick for the count of er visits

Count ( {<IMPUTED_CLAIMNO= p({<pos={'20','23'}>}IMPUTED_CLAIMNO) >}DISTINCT IMPUTED_CLAIMNO)

It's not pretty and it took me too long for something that takes less than a minute to write and run in SQL but at least it works and I learned something. Hopefully as knowledge of QlikView increases I will find, or you will provide, a more efficient way of getting there.

It was a syntax issue.

Steve

Re: using a where clause in QlikView syntax

marcus_sommer — Sat, 16 Jan 2016 17:46:21 GMT

This should also work for 4. if it's from a table like mentioned in your initial post:

Count ({< pos={'20','23'}>} DISTINCT IMPUTED_CLAIMNO)

- Marcus

Re: using a where clause in QlikView syntax

maxgro — Sat, 16 Jan 2016 18:28:03 GMT

regarding your first question

Select pat_id, count (distinct IMPUTED_CLAIMNO) 'ER Visits'

from [dbo].[OvarianCA_Year3_Claims]

where pos in ('20','23')

group by pat_id

you can make a chart (straight table) with pat_id as dimension and count(distinct IMPUTED_CLAIMNO) as expression

also add a listbox for pos and in few seconds you can query the qlik database and get (I think) all your answers without the need to change a SQL query, just click on the values you want to filter.

Regarding your second question , if you can, post your .qvw or a small example.

You don't have to create in Qlik the same single SQL query but a database that you can use to discover the data; every time you click (filter) in Qlik you're querying the Qlik db. Don't try to create a single SQL query in Qlik.