3 Replies Latest reply: Jun 1, 2017 7:33 AM by Marco Giannuzzi RSS

    How to write sql query in expression

    Avinash S

      Hi Guys,

       

      I have to find the age of persons and put them in different age category.Differnt age category is defines as

      Less than 20

      Between 20 to 30

      Between 30 to 40

      and so on...

       

      Query i have written in SQL and its working fine, How do i replicate the same in qliksense under expression.

       

      "select Age_category, count(distinct clientnumber) from (select a.clientnumber,

      case when datediff(yy,dateofbirth, getdate())<20 then 'Less than 20'

      when datediff(yy,dateofbirth, getdate())>=20 and datediff(yy,dateofbirth, getdate())<30 then 'Between 20 to 30'

      when datediff(yy,dateofbirth, getdate())>=30 and datediff(yy,dateofbirth, getdate())<40 then 'Between 30 to 40'

      when datediff(yy,dateofbirth, getdate())>=40 and datediff(yy,dateofbirth, getdate())<50 then 'Between 40 to 50'

      when datediff(yy,dateofbirth, getdate())>=50 then 'more than 50'

      else Null

      end as Age_category"

       

      Regards,

      Avinash

        • Re: How to write sql query in expression
          Sunny Talwar

          May be this

           

          If(Age(Today(), dateofbirth) < 20, Dual('Less than 20', 1),

          If(Age(Today(), dateofbirth) < 30, Dual('Between 20 to 30', 2),

          If(Age(Today(), dateofbirth) < 40, Dual('Between 30 to 40', 3),

          If(Age(Today(), dateofbirth) < 50, Dual('Between 40 to 50', 4), Dual('More than 50', 5))))) as Age_category

            • Re: How to write sql query in expression
              Sunny Talwar

              Or this as a calculated dimension

              Aggr(If(Age(Today(), dateofbirth) < 20, Dual('Less than 20', 1),

              If(Age(Today(), dateofbirth) < 30, Dual('Between 20 to 30', 2),

              If(Age(Today(), dateofbirth) < 40, Dual('Between 30 to 40', 3),

              If(Age(Today(), dateofbirth) < 50, Dual('Between 40 to 50', 4), Dual('More than 50', 5))))), client_number)

               

              Expression

              Count(DISTINCT client_number)

            • Re: How to write sql query in expression
              Marco Giannuzzi

              If the output should be like this in QlikSense:

               

               

              I considered an input file like this:

               

              And I used the following, with the help of the class:

               

               

              Table:

              LOAD

                  Clientnumber,

                 DateofBirth,

                YEAR(Today())- YEAR(DateofBirth) as difference

               

              FROM [lib://Prove/caso3.xlsx]  // I used a folder connector in order to import the file above

              (ooxml, embedded labels, table is Foglio1);

               

               

              //I used the class in order to easily achieve the ranges

              Groups:

              LOAD * INLINE [

              Start,Stop,Group

              0,20, Less than 20

              20,30, Between 20 and 30

              30,40, Between 30 and 40

              40,50, Between 40 and 50

              50,, More than 50

               

              ];

               

              IntervalMatch(difference)

              left join(Groups)

              LOAD Start,Stop RESIDENT Groups;

               

              DROP Fields Start,Stop;