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: 
arulsettu
Master III
Master III

count for employee and dependent shows incorrect

Hi all,

          i am trying to show the count of employee and dependent by age wise in barchart. but i am getting incorrect values. i checked the data model also.

my approval date format like this

Untitled.png

so i did this in back end to get date

     date(Floor(MP_APPR_DT)) as APPROVED_DATE

from this APPROVED_DATE created master calendar.

while checking in DB the values getting in QV is incorrect.

i dont know what cause the issue.

please check the attached app.

this the expected output for the age range

Untitled.png

some one please suggest some solution. its really urgent.

Thanks

29 Replies
arulsettu
Master III
Master III
Author

yes i commented all other tables and distinct employee ID gives this result 173714

rubenmarin

So you'll never get the 192.807 distinct employee IDs, can you paste the query done in DB?

arulsettu
Master III
Master III
Author

here

arulsettu
Master III
Master III
Author

i just run this query in DB

select count (distinct ME_EMP_ID) from T_MD_EMPLOYEE

i got this 181533

arulsettu
Master III
Master III
Author

any idea guys?

rubenmarin

Not yet, can you try a simpler query like:

SELECT ME_AGE, ME_EMPLOYEE_YN, COUNT(DISTINCT ME_EMP_ID)

WHERE EXISTS (SELECT MP_BI_ID  FROM T_MD_POLICY WHERE MP_BI_ID=ME_MP_BI_ID AND TRUNC(MP_APPR_DT) BETWEEN '01-JAN-2015' AND '10-SEP-2015')

Note: Changed the date to 9/10/2015 as it's the max APPROVAL_DATE in sample qvw.

rubenmarin

I forget the "GROUP BY ME_AGE, ME_EMPLOYEE_YN"

arulsettu
Master III
Master III
Author

i got this

0N2106
0Y36
1N2890
1Y23
2N3066
2Y5
3N3054
3Y11
4N2982
4Y14
5N2946
5Y7
6N2697
6Y2
7N2623
7Y8
8N2379
8Y7
9N2208
9Y2
10N2106
10Y2
11N2011
11Y2
12N1836
12Y2
13N1649
13Y1
14N1547
14Y2
15N1465
15Y1
16N1345
16Y7
17N1246
17Y13
18N929
18Y175
19N637
19Y709
20N571
20Y1504
21N481
21Y2393
22N434
22Y3625
23N484
23Y4769
24N534
24Y6385
25N662
25Y8064
26N806
26Y9675
27N961
27Y10565
28N1076
28Y11232
29N1076
29Y11335
30N1138
30Y11236
31N1261
31Y11187
32N1198
32Y10869
33N1176
33Y10376
34N1121
34Y10014
35N1145
35Y9110
36N1065
36Y8476
37N912
37Y7969
38N861
38Y7473
39N821
39Y6939
40N748
40Y6443
41N710
41Y5955
42N636
42Y5592
43N595
43Y5312
44N597
44Y5023
45N537
45Y4683
46N455
46Y4234
47N415
47Y3822
48N371
48Y3465
49N358
49Y3133
50N349
50Y2889
51N297
51Y2768
52N283
52Y2568
53N257
53Y2284
54N222
54Y2182
55N183
55Y2006
56N166
56Y1838
57N143
57Y1602
58N108
58Y1422
59N89
59Y1257
60N73
60Y1001
61N68
61Y820
62N58
62Y636
63N40
63Y440
64N34
64Y352
65N32
65Y220
66N18
66Y151
67N19
67Y124
68N12
68Y84
69N15
69Y65
70N9
70Y32
71N2
71Y28
72N10
72Y19
73N9
73Y31
rubenmarin

Sorry but doesn't tells me anything, my bad.  The group above 60 with ME_EMPLOYEE_YN='Y' gives 3002

arulsettu
Master III
Master III
Author

one thing same emp id present in different age but the policy tans id is unique

empid,age,pol_id

0353063873
0352680587
0353457981
0353176244
0352469281
0353570361
0352572759
0354079043
0353680227
0353483714

is this make any sense