distinct problems with a detailed data table

[#1]

d

[#2]

Hi Aissam,

Try this : Count(Distinct JOB_NUMBER) instead of Sum(Quantity)

[#3]

Thanks,

This works, but what if Quantity is 2 for a JOB_NUMBER, Instead of 1??

Regards,

Aissam

[#4]

In your source, Quantity is in relation with JOB_NUMBER, ProductCode, CUSTOMER_NUMBER and PRODUCT_LINE, so you can have different values of Quantity for Product_Lines of a same ProductCode, right ?

So what do you want in fact in your report ? If you want the Total Quantity, keep your Sum(Quantity)...

If you want to count the Product_Line, use Count() instead...

In fact, if you have 2 for Quantity, it's probably for one Special PRODUCT_LINE of your ProductCode, not for all (or not) ! So you must choose between :

• if the quantity does not depend on the PRODUCT_LINE, but on the JOB_NUMBER, just take the value of the field (or the Average, if it's always the same....)
• if the quantity depend on the PRODUCT_LINE, you must Sum(), Count() or choose one value !

Hope this helps...

[#5]

Hi there,

The best things is to create a new field as counter with for each row the same value = 1.

Then you do your count on this field.

[#6]

It works,

I used ProductCode instead of PRODUCT_LINE to create the ProductDescription. Then I used the average (Quantity).

Thanks guys,

Regards,

Aissam