Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Conditional deduplication based on multiple columns

Hi everyone,   I need to write a very specific deduplication query and I am wondering if you can help me out. We have an ecommerce site and I am getting a daily CSV file that has transactional info...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous 

    Try this code.

    Remove Duplicate =
SUMMARIZE (
    FILTER (
        'Table',
        IF (
            VAR _COUNTROW =
                CALCULATE (
                    COUNTROWS ( 'Table' ),
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Master Order Number] = EARLIER ( 'Table'[Master Order Number] )
                            && 'Table'[Item ID] = EARLIER ( 'Table'[Item ID] )
                    )
                )
            RETURN
                _COUNTROW > 1,
            'Table'[RANK] <> 1
                || 'Table'[Invoice Date] <> BLANK (),
            'Table'[RANK] = 1
        )
    ),
    'Table'[Order Number],
    'Table'[Master Order Number],
    'Table'[Order Date],
    'Table'[Invoice Date],
    'Table'[Item ID]
)

    Result is as below.

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Anonymous -- even closer but one thing is still missing. I still want to count orders that were placed but not yet invoiced. Only when there were duplicate Item IDs within a Master Order # that had blank Invoice Dates did I want to discard them from the total.

 

Consider the Master Order # below, #10007106. It does not have an invoice date (yet) but does not have any duplicate Item IDs. In other words, it has not yet been invoiced but has not been double counted. It should still be counted in total sales (I created a separate metric for invoiced sales).

 

So how could I modify either the deduplication table or the rank column to account for this? Again, thanks for all your help and for sticking with me on this one.

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous 

Try this code.

Remove Duplicate =
SUMMARIZE (
    FILTER (
        'Table',
        IF (
            VAR _COUNTROW =
                CALCULATE (
                    COUNTROWS ( 'Table' ),
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Master Order Number] = EARLIER ( 'Table'[Master Order Number] )
                            && 'Table'[Item ID] = EARLIER ( 'Table'[Item ID] )
                    )
                )
            RETURN
                _COUNTROW > 1,
            'Table'[RANK] <> 1
                || 'Table'[Invoice Date] <> BLANK (),
            'Table'[RANK] = 1
        )
    ),
    'Table'[Order Number],
    'Table'[Master Order Number],
    'Table'[Order Date],
    'Table'[Invoice Date],
    'Table'[Item ID]
)

Result is as below.

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you very much Anonymous this has done the trick. Appreciate your expertise and assistance.