Split data by given level of detail

Question

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.

CompanyDateScenarioCompanyA31.12.2018ActualCompanyA31.12.2018BudgetCompanyA31.03.2019BudgetCompanyB30.06.2017BudgetCompanyB31.12.2017ActualCompanyB31.12.2017BudgetCompanyB31.03.2018BudgetCompanyC30.04.2019BudgetCompanyD30.06.2019ActualCompanyD31.12.2019BudgetCompanyD30.06.2020Actual

AngelosPachis · Accepted Answer

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