Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to write sql query in expression

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

3 Replies
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

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)

Anonymous
Not applicable
Author

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;