Grouping Invoices By Allocation

[Klachner]

I am trying to group this data set attached. The data sets include invoice number, location, account, and balance. I want to find invoices where their allocation isn't entirely to Europe. So that would be invoices 1 and 3 and breakout the dollar amounts associated with the accounts. 

So I would want to pull Invoice 1 and 3 and see the breakout of accounts 1000, 2000, 5000 per invoice

[aatalai]

I would use the summarise tool to group by invoice number and count number of records.

After the initial data I would filter for countries you want/don't want. Then d the same group by invoice number and count number of records. Then join the two summarised data by invoice number, and then use another filter for the 2 record numbers being the same. If they are not then it's not entierely in europe and if ti is then they are allocated.

Hope this helps

[JTCairns]

on a sub part of the main flow Just group by invoice number count distinct location, join back on Invoice number then filter where count doesn't = 1 

[Raj]

so, to solve this problem, you will need to add a summarize tool where you will group by Invoice number and Location
this will populate the combinations of invoice numbers and locations then you can use a filter tool to remove Europe or any other field as per requirement then at last you will ad a join tool to pull the records from input and output of FIlter tool.

hope this Help.
@Klachner please reach out in case of Confusion.