Hi,

I have a report that when run it comes up with duplicate values for the same transaction. How can i filter that out?

This message has been edited. Last edited by: Kerry,

Would SUM instead of PRINT be the answer you're looking for?

Hi,

I tried that, it sums up all the values, i don't want that.

My report is looking for purchases over $25,000 on a day to day basis, this is what i get when i run the report.

ORDER_DIVISION SUPPLIER_NUMBER SUPPLIER_NAME PURCHASE_ORDER_NUMBER BUYER_CODE ORDER_DATE GROSS_AMOUNT
O 02205 BELDEN CANADA INC. 3 76606 2009/07/03 93733.2200
O 02205 BELDEN CANADA INC. 3 76606 2009/07/03 93733.2200
O 02205 BELDEN CANADA INC. 3 76606 2009/07/03 93733.2200
O 02205 BELDEN CANADA INC. 3 76606 2009/07/03 93733.2200
O 02205 BELDEN CANADA INC. 3 76606 2009/07/03 93733.2200
O 02205 BELDEN CANADA INC. 3 76606 2009/07/03 93733.2200

Are you doing any joins?

Please post your code.

TABLE FILE FCT_PURCH
PRINT
'FCT_PURCH.FCT_PURCH.ORDER_DIVISION'
'FCT_PURCH.FCT_PURCH.BRANCH_CODE'
'FCT_PURCH.FCT_PURCH.SUPPLIER_NUMBER'
'FCT_PURCH.FCT_PURCH.SUPPLIER_NAME'
'FCT_PURCH.FCT_PURCH.PURCHASE_ORDER_NUMBER'
'FCT_PURCH.FCT_PURCH.BUYER_CODE'
'FCT_PURCH.FCT_PURCH.ORDER_DATE'
'FCT_PURCH.FCT_PURCH.GROSS_AMOUNT'
HEADING
""
FOOTING
""
WHERE FCT_PURCH.FCT_PURCH.GROSS_AMOUNT GT 25000;
WHERE FCT_PURCH.FCT_PURCH.ORDER_DATE EQ 20090703;

Is there a join that is causing the duplicate rows or is it normal that duplicate rows exist in the data?

This may be one possible solution:

TABLE FILE FCT_PURCH
SUM
FST.FCT_PURCH.FCT_PURCH.GROSS_AMOUNT
BY LOWEST 1 FCT_PURCH.FCT_PURCH.ORDER_DIVISION
BY LOWEST 1 FCT_PURCH.FCT_PURCH.BRANCH_CODE
BY LOWEST 1 FCT_PURCH.FCT_PURCH.SUPPLIER_NUMBER
BY LOWEST 1 FCT_PURCH.FCT_PURCH.SUPPLIER_NAME
BY LOWEST 1 FCT_PURCH.FCT_PURCH.PURCHASE_ORDER_NUMBER
BY LOWEST 1 FCT_PURCH.FCT_PURCH.BUYER_CODE
BY LOWEST 1 FCT_PURCH.FCT_PURCH.ORDER_DATE
END

Hi,

There is no Join on this report, it's normal that duplicate rows exists for the data. is there a way to remove the duplicate?

How about my suggestion above?

It works, but it only show one transaction, i should have 2 transaction for that date.

quote:

Originally posted by Amit Somaru:
It works, but it only show one transaction, i should have 2 transaction for that date.

Why do you say that you have 2 transactions for that date?
Which is the field that can identify the two transactions?

My suggested code will give you one report row for the set of BY LOWEST 1 columns. From the example data you posted, it appears that there should only be one row as output.

quote:

BY LOWEST 1 FCT_PURCH.FCT_PURCH.PURCHASE_ORDER_NUMBER

Francis is on the right track, but his code will always give you only one record. I have the feeling that the above line is the unique identifier for the transaction. It is the only one you will need the "BY LOWEST1" on. Try removing it from all of your other BY statements.

Instead of:

TABLE FILE FCT_PURCH SUM FST.FCT_PURCH.FCT_PURCH.GROSS_AMOUNT
BY LOWEST 1 FCT_PURCH.FCT_PURCH.ORDER_DIVISION
...
BY LOWEST 1 FCT_PURCH.FCT_PURCH.ORDER_DATE
END

could you do something like:

TABLE FILE FCT_PURCH SUM FST.FCT_PURCH.FCT_PURCH.GROSS_AMOUNT
WHERE FCT_PURCH.FCT_PURCH.ORDER_DIVISION NE LAST FCT_PURCH.FCT_PURCH.ORDER_DIVISION
...
WHERE FCT_PURCH.FCT_PURCH.ORDER_DATE NE LAST FCT_PURCH.FCT_PURCH.ORDER_DATE
END