Hiding a genuine duplicate row

[Skykit]

Hi all,

I have potentially a strange query unless I'm looking at this all wrong.

I have a list report with 10 columns.  Where most are fine, there are instances where two rows are identical including the transaction ID but have one value different in a different column.
For instance:

Order 003 is split into two lines because it's "paid method" was credit AND cash
Order 001 has just one line because it's "paid method" was just credit

Currently, the paid method is a case statement to state when Cash then show Cash, otherwise it's credit

Is there a way I could get rid of one of the 003 lines - for instance, if I only wanted to see the credit line and not the cash line

As you can see, it is a genuine duplicate for the order but the client does not want to see two lines and only wants to see one specific line.  This would be the case in all cases where there are two methods, only the credit line should show.

Thanks in advance,
AJ

[charlie]

Query1 : This is the same query which you are using now (fetching all data with duplicates).
Query2 : Select all records which have multiple rows for a transaction ID (put a logic as per your data, can use a self join to find such records)

From Query 2 filter the record that you want to show for the transaction (pay mode like %AND%) --> Query3

Query1 except (or minus) Query2 --> Query4

Final Query = Query3 Union Query4

I am sure there must be a better way to do this but hope this will get you the result you're looking for :)

[bi4u2]

With auto group and summarize, if you remove your 'Paid Method' from the face of the report it will show only the one row.

If it is a requirement to show paid method, you can create a data item that 'counts' the number of payment methods per order and if it is >1 then just show 'Credit' - this would be a simple if/then/else statement in the 'Paid Method' column.