Split data by given level of detail

[onanm]

Hi,

I am trying to split my data into two groups as 'best option' and 'least option' by given level of detail. I am planning to apply some calculations/transformation to 'best option' data table, then simply union with 'least option'. Below is a simple table that illustrates what I am trying to do. In this table, 'Actual' scenario has priority over 'Budget' scenario given same company and date. In other words, for a given company and date, if I have both 'Actual' and 'Budget' scenario, choose 'Actual' scenario. I have highlighted the cases below. But I do not want to remove least option of 'Budget'. I just want to keep as separate data table to be later added to analysis

Thanks a lot for your help.

Company	Date	Scenario
CompanyA	31.12.2018	Actual
CompanyA	31.12.2018	Budget
CompanyA	31.03.2019	Budget
CompanyB	30.06.2017	Budget
CompanyB	31.12.2017	Actual
CompanyB	31.12.2017	Budget
CompanyB	31.03.2018	Budget
CompanyC	30.04.2019	Budget
CompanyD	30.06.2019	Actual
CompanyD	31.12.2019	Budget
CompanyD	30.06.2020	Actual

[AngelosPachis · Accepted solution]

Hi @onanm ,

What you can do is to use a summarize tool to count the number of scenarios per company and date. Then with a filter tool, you can isolate the Budget scenarios for those companies that had more that one scenarios on a certain date 

So out of the T output anchor you will have

And out of the F output anchor you will have the rest of your records.

You can go on and process them separately, before bringing them back together with a union tool.

Hope that helps,

Angelos

[onanm]

Perfect Angelos, thanks a lot!