topic Re: Count function in straight table in QlikView

Count function in straight table

Mon, 13 Oct 2014 02:37:54 GMT

Hi, I want to count how many of each PO Num exists so I did this simple straight table:

PO Num	Count (PO_Num)
1234	25

I did another straight table and added in the other variables:

Date	PO Num	Store	Line Cost	Unit Cost	Count (PO_Num)
30/09/14	1234	A	543.6	54.36	5
30/09/14	1234	A	543.6	54.36	5
30/09/14	1234	A	265.8	54.36	5
30/09/14	1234	A	265.8	54.36	5
30/09/14	1234	A	1653.2	54.36	5

May I know how to get this:

PO Num	Count (PO_Num)
1234	5

Date	PO Num	Store	Line Cost	Unit Cost	Count (PO_Num)
30/09/14	1234	A	543.6	54.36	1
30/09/14	1234	A	543.6	54.36	1
30/09/14	1234	A	265.8	54.36	1
30/09/14	1234	A	265.8	54.36	1
30/09/14	1234	A	1653.2	54.36	1

I've already tried formulas such as Count(PO_Num), textcount(PO_Num) and aggr(Count(PO_Num),PO_Num,Store,Date) but I still got 25 as the total count.

Thanks.

Re: Count function in straight table

jagan — Mon, 13 Oct 2014 02:44:26 GMT

Hi,

Try like this

For Table1 :

=Max(Aggr(Count(PO_Num), Date, PO_Num, Store, [Line Cost], [Unit Cost]))

For Table 2:

Count(DISTINCT PO_Num)

Hope this helps you.

Regards,

Jagan.

Re: Count function in straight table

Mon, 13 Oct 2014 03:14:39 GMT

Thanks, but if I use count(distinct the result will only be 1 record for that PO_Num, but it's supposed to be 5 records

Re: Count function in straight table

jagan — Mon, 13 Oct 2014 03:41:22 GMT

HI,

For this use : =Max(Aggr(Count(PO_Num), Date, PO_Num, Store, [Line Cost], [Unit Cost]))

PO Num	Count (PO_Num)
1234	5

For this : Count(DISTINCT PO_Num) - Using distinct you will get 1, I think this is what you are expecting.

Date	PO Num	Store	Line Cost	Unit Cost	Count (PO_Num)
30/09/14	1234	A	543.6	54.36	1
30/09/14	1234	A	543.6	54.36	1
30/09/14	1234	A	265.8	54.36	1
30/09/14	1234	A	265.8	54.36	1
30/09/14	1234	A	1653.2	54.36	1

Regards,

Jagan.

Re: Count function in straight table

Mon, 13 Oct 2014 05:24:56 GMT

Thanks, but actually I only want this table:

PO Num	Count (PO_Num)
1234	5

This larger table is only for me to check why the count of PO_Num is 25 instead of 5:

Date	PO Num	Store	Line Cost	Unit Cost	Count (PO_Num)
30/09/14	1234	A	543.6	54.36	1
30/09/14	1234	A	543.6	54.36	1
30/09/14	1234	A	265.8	54.36	1
30/09/14	1234	A	265.8	54.36	1
30/09/14	1234	A	1653.2	54.36	1

I still don't get my desired table using ' =Max(Aggr(Count(PO_Num), Date, PO_Num, Store, [Line Cost], [Unit Cost]))'.

Is there any chance you might know why each record for PO_Num '1234' is duplicated 5 times? Because now I am getting this table:

PO Num	Count (PO_Num)
1234	25

And to see why there are 25 records for PO_Num '1234', I did this next table:

Date	PO Num	Store	Line Cost	Unit Cost	Count (PO_Num)
30/09/14	1234	A	543.6	54.36	5
30/09/14	1234	A	543.6	54.36	5
30/09/14	1234	A	265.8	54.36	5
30/09/14	1234	A	265.8	54.36	5
30/09/14	1234	A	1653.2	54.36	5

Thanks,

Re: Count function in straight table

jagan — Mon, 13 Oct 2014 05:37:15 GMT

Hi,

You have different line costs that is the PO_Num count is duplicating.

Regards,

Jagan.