Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi i am trying to count the policy so did like this
count(distinct employee_id).
and dimension is age band
like this
if(ME_AGE>=0 and ME_AGE<=6,'0-6',
if(ME_AGE>=7 and ME_AGE<=18,'7-18',
if(ME_AGE>=19 and ME_AGE<=30,'19-30',
if(ME_AGE>=31 and ME_AGE<=45,'31-45',
if(ME_AGE>=46 and ME_AGE<=60,'46-60',
if(ME_AGE>=60,'60')))))) as age_band
but the result getting is not correct. while checking in db output is little differs in qlikview.
here i attached a sample app please check and let me know what i am doing wrong.
this is the expected output
14594 | 0 to 6 age |
15088 | 7 to 18 age
|
74968 | 31 to 45 age
|
please check my sample app above
is this enough jagan na
one thing here employee_id have multiple age entry. because it has various policy id.
like this
emp_id,age,pol_id
035 | 34 | 57981 |
035 | 30 | 63873 |
035 | 24 | 69281 |
035 | 35 | 70361 |
035 | 25 | 72759 |
035 | 31 | 76244 |
035 | 40 | 79043 |
035 | 36 | 80227 |
035 | 26 | 80587 |
035 | 34 | 83714 |
please share some idea
Thanks
Hi Arul,
Try something like this
Data_Temp:
LOAD
*
INLINE [
emp_id,age,pol_id
035, 34, 57981
035, 30, 63873
035, 24, 69281
035, 35, 70361
035, 25, 72759
035, 31, 76244
035, 40, 79043
035, 36, 80227
035, 26, 80587
035, 34, 83714];
Data:
LOAD
emp_id, age, pol_id, flag,
if(age>=0 and age<=6,'0-6',
if(age>=7 and age<=18,'7-18',
if(age>=19 and age<=30,'19-30',
if(age>=31 and age<=45,'31-45',
if(age>=46 and age<=60,'46-60',
if(age>=60,'60')))))) as age_band;
LOAD
emp_id, age, pol_id,
If(Peek('emp_id') <> emp_id, 1, 0) AS flag
RESIDENT Data_Temp
ORDER BY emp_id, age desc, pol_id;
DROP TABLE Data_Temp;
Now in chart use like this:
Dimension: age_band
Expression: Count({<flag={1}>} emp_id)
Hope this helps you.
Regards,
Jagan.
getting same old output.
if i select particular policy id the count of emp id with age wise is correct. but over all count shows wrong.
what will be the issue?
Thanks
any idea to solve this. its emergency.
Thanks
Hi,
Arul come up with a clear example, take one emp_id with multiple ages and your expected age_band for this emp_id, then only it is easier to solve this.
Regards,
Jagan.
if i choose emp id 035
chart showing like this
but the expected output is
emp, age
035 | 34 |
035 | 30 |
035 | 24 |
035 | 35 |
035 | 25 |
035 | 31 |
035 | 40 |
035 | 36 |
035 | 26 |
035 | 34 |
Use Sum() instead of Count().