Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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)
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;