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!
