Select Tool - Variable/Criteria Based

[C_Allen]

Summary: Based on a variable (in this case month); The columns i include in the select and rename need to change.

The setup:

• DATASET contains columns: Month, Actuals, Plan, Forecast39, Forecast57, Forecast66, Forecast84, Forecast93
• I have a second CONTROL_TABLE i've imported that contains columns: Month and Forecast

Select behavior:

• If we are in September I am selecting columns: Month, Actuals, Plan, Forecast84 (Renamed 'FCST')
• If we are in October I am selecting columns: Month, Actuals, Plan, Forecast93 (Renamed 'FCST')

-------

In an ideal setup, i would find The Month from the DATASET (will be the same for whole dataset), Find the corresponding Forecast from the CONTROL_TABLE, and then only select the corresponding forecast based on the result of the mapping of Month to Forecast from the CONTROL_TABLE;

I can -

Isolate the month from the DATASET, Return the corresponding Forecast from the CONTROL_TABLE;

The ask -

The part I am looking to gain assistance on is the selecting columns based on the mapping.

Avenues Explored:

• I Can do a complicated if tree that determines month and takes a path based on month, this works but is cumbersome

• Some type of CASE statement would be better than an If tree but I can seem to find one

• Dynamic Select appears that it may be the right way to go, but I cant quite sort it out

Thoughts are greatly appreciated!

[dougperez]

This month is the current month or you will be able to run 2-3 months in one execution?

[dougperez]

This should help you

[C_Allen]

To answer your previous post;  Each run would only have a single Month. 

Your solution is workable, I'll use it as a starting point.  I've not stress tested this solution, but I have a feeling it will bog down a bit,  >1mill rows in dataset x 6 forecasts makes for a lot of cross tab transformation. 

[dougperez]

I did another one, this will do the same thing but without transposing and cross tabing your entire table.

This workflow is more complicated and have many steps, but should do the trick too.

If this helps, mark my resoponse as solution 😄

[C_Allen]

Apologies for the late response.  Many moving projects over here just now circling back.  Let me evaluate this solution.  Thank you in advance and i'll be Sure to mark it if I can apply this! 

[Christina_H]

I might be missing something but this looks like a simple job for a dynamic rename tool.  Just identify and rename the correct forecast column then use a regular select tool to select the columns for downstream processing.

[C_Allen]

The columns on the input data are called things like "Forecast39", "Forecast66", and "Forecast93".  All of these appear in the data, but for any given month, i only need one of them, and need to call it "FCST"; 

The part i'm finding hard is saying: 

Based on column "Month" i can identify its currently 'May' and in 'May' i need to use "Forecast66" so rename "Forecast66" to "FCST" and dont rename/select the others. But in October, i need to do the same thing this time with "Forecast93" and rename it "FCST"