topic Re: why is count function in straight table multiplying my records by 5 in QlikView

why is count function in straight table multiplying my records by 5

Mon, 13 Oct 2014 07:51:16 GMT

Hi, I have only 5 records of PO_num '1234' as shown in the table below:

Date	PO_num	Store	Line Cost	Qty	Unit Cost
30/09/14	1234	A	543.6	4	54.36
30/09/14	1234	A	543.6	6	54.36
30/09/14	1234	A	265.8	8	54.36
30/09/14	1234	A	265.8	2	54.36
30/09/14	1234	A	1653.2	5	54.36

I want to create a column that displays how many records exists for PO_num '1234'. However, when I use count function in straight table, it multiplies each record by 5 :

PO_num	Count (PO_num)
1234	25

Then I used this straight table to check:

Date	PO_num	Store	Line Cost	Qty	Unit Cost	Count(PO_num)
30/09/14	1234	A	543.6	4	54.36	5
30/09/14	1234	A	543.6	6	54.36	5
30/09/14	1234	A	265.8	8	54.36	5
30/09/14	1234	A	265.8	2	54.36	5
30/09/14	1234	A	1653.2	5	54.36	5

How can I achieve this result? :

PO_num	Count (PO_num)
1234	5

Thanks.

Re: why is count function in straight table multiplying my records by 5

ariel_klien — Mon, 13 Oct 2014 07:55:19 GMT

Hi,

I think that something on your script is multiply the table,

try to load the table with Distinct

or use count(distinct PO_num) - less recommended

Ariel

Re: why is count function in straight table multiplying my records by 5

maleksafa — Mon, 13 Oct 2014 07:55:36 GMT

count(Distinct PO_num) will give you 5, you need to check your data model how do you have the PO_num field there and you will be able to find why it is multiplied by 5.

Re: why is count function in straight table multiplying my records by 5

PrashantSangle — Mon, 13 Oct 2014 07:56:56 GMT

Hi,

Take PO_num in list box->Property->General->select Frequency.

See how may frequency you are getting for PO_num

To acheive desired result you have to use AGGR()

try like

count(Aggr(Distinct PO_num),Date,Store)

Regards

Re: why is count function in straight table multiplying my records by 5

Mon, 13 Oct 2014 08:03:01 GMT

same is not happening on my side

check ur script again might be you doing wrong somewhere

Re: why is count function in straight table multiplying my records by 5

mdmukramali — Mon, 13 Oct 2014 08:13:47 GMT

Dear,

for me i'm getting correct.

Re: why is count function in straight table multiplying my records by 5

Mon, 13 Oct 2014 08:22:48 GMT

Thanks so much. But it's actually 'count(Aggr(Distinct PO_num,Date,Store))'. Another thing is I have this table in excel (the duplicated records are supposed to be):

PO_num	Date	Store	Dr	Unit Price	Value
345	30/09/14	A	7645	234	7645
345	30/09/14	A	7645	234	7645
345	30/09/14	A	2345	234	2345
345	30/09/14	A	2345	234	2345
345	30/09/14	A	3456	234	3456

But when I created this table box in Qlikview it combines the duplicated records into 1 record:

PO_num2	Date	Store	Dr	Unit _Price	Value
345	30/09/14	A	7645	234	7645
345	30/09/14	A	2345	234	2345
345	30/09/14	A	3456	234	3456

I applied the same formula 'count(Aggr(Distinct PO_num2,Date,Store,Dr,Unit_Price,Value))' in a straight table but it counts only 3 records of PO_num2 '345' when it's supposed to be 5. How can I achieve this straight table? :

PO_num	Count (PO_num)
345	5

Thanks.

Re: why is count function in straight table multiplying my records by 5

PrashantSangle — Mon, 13 Oct 2014 08:45:15 GMT

Hi,

Try it like this,

count(Aggr(Count(Distinct PO_num),Date,Store,Dr,Unit_Price,Value)

Regards

Re: why is count function in straight table multiplying my records by 5

Mon, 13 Oct 2014 09:06:45 GMT

Thanks, but I still get

PO_num	count(Aggr(Count(Distinct PO_num),Date,Store,Dr,Unit_Price,Value))
345	3