topic Re: count issue in QlikView

count issue

sudeep_d — Tue, 26 Feb 2013 07:53:30 GMT

contract yearmnth type

123 201206 a

123 201205 b

123 201204 c

543 201209 d

543 201209 e

543 201208 f

In the above e.g, i want the count as 1 for contract 123 since the max month is appearing once i.e 201206

but in 543,count should be 2 since maxmonth for this contract appears twice.. i.e 201209

Please suggest me the required expression.

Thanks

Re: count issue

sujeetsingh — Tue, 26 Feb 2013 08:25:38 GMT

Just use countdistinct() and put values and conditions you can easily get a Status as 1 2 or what you want.

Re: count issue

Tue, 26 Feb 2013 08:31:49 GMT

Hi Sudeep,

Please find the attached doc, hope it helps you.

Re: count issue

Tue, 26 Feb 2013 08:33:28 GMT

hi,

you can acchieve this with the following script

table1:

LOAD * INLINE [
    contact, yearmnth, type
    123, 201206, a
    123, 201205, b
    123, 201204, c
    543, 201209, d
    543, 201209, e
    543, 201208, f
];

table2:
NoConcatenate

LOAD
contact,
max(yearmnth) AS max_month
RESIDENT table1
GROUP BY contact;
LEFT JOIN

LOAD
contact,
yearmnth AS max_month,
Count(yearmnth) AS count
RESIDENT table1
GROUP BY contact, yearmnth;

DROP TABLE table1;

Regards vicky

Re: count issue

sudeep_d — Tue, 26 Feb 2013 08:58:03 GMT

Hi yasin, i want it in a text box when i select 123 count should be 1 and when i select 543 count should be 2

hi vicky,i want it as expression not in script level..

thanks

Re: count issue

manideep78 — Tue, 26 Feb 2013 09:18:00 GMT

try this

=Count({<yearmnth={"$(=max(yearmnth))"}>}contract)

See attached qvw

Hope this helps.

Re: count issue

sudeep_d — Tue, 26 Feb 2013 12:08:22 GMT

Hi manideep,it worked

Thanks