6 Replies Latest reply: Jan 16, 2016 1:28 PM by Massimo Grossi RSS

    using a where clause in QlikView syntax

    Steve Eisenberg

      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
          Ryan Ridley

          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
              Steve Eisenberg

              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
                Steve Eisenberg

                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

                    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
                      Massimo Grossi

                      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.