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

[#1]

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.

[ariel_klien]

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

BR

Ariel

[maleksafa]

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.

[PrashantSangle]

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

[#5]

same is not happening on my side

check ur script again might be you doing wrong somewhere

[mdmukramali]

Dear,

for me i'm getting correct.

[#7]

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.

[PrashantSangle]

Hi,

Try it like this,

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

Regards

[#9]

Thanks, but I still get

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