Forum Discussion
Conceptual Question: Data Replacement and Calculated Columns
Hello tex628,
Thank you for the response!
Your unique identifier is appears to be a combination of department ID and account ID. Assuming "Dept Codes" and "Account Codes" hold all possible values for each dimension you should merge these two tables to create a single table holding all possible unique identifiers that can appear in your fact tables.
I didn't think to merge the two reference tables... how would you recommend me going about that? My issue is that the calculated columns in 'FAOP' are looking through each row of data within the table, but not every COA in 'FAOP' appears in 'Actuals', and vice versa. Calculated columns can't be calculated for more rows than appear, so how can I get it to calculate for every single COA option? Maybe move the calculated columns to a different table that contains each COA? Not sure how to still be able to lookup data in FAOP/Actuals then...
This very much depends on how you load the data. Calculated columns wont be deleted when you add additional data, just recalculated depending on what you add. You can never make a calculated column not calculate, instead you have to tell it how to behave.
I understand what you are saying about dropping in new data! Follow up question: when I drop in new data with potential new headers ("June 2019" instead of "June 2018" for example), will it not cause all of my visualizations in my report view to need to be recreated? I will also need to change the calculated column formulas too, correct?
Thank you for the help,
Kathleen
I'm assuming that the source of your data are excel files.
In that case your columns need to have the same headers, if not you will be left with two paralell columns where your calculated column is only calculating one of them:
| Jun-18 | Jul-18 |
| 10 | 20 |
| 15 | 15 |
| 10 | 30 |
If the column headers are identical the columns will be appended ontop of eachother. This is why i would recommend moving the month into a column in the table instead:
| Amount | Month |
| 10 | Jun-18 |
| 15 | Jun-18 |
| 10 | Jun-18 |
| Amount | Month |
| 20 | Jul-18 |
| 15 | Jul-18 |
| 30 | Jul-18 |
And then append, which would give you:
| Amount | Month |
| 10 | Jun-18 |
| 15 | Jun-18 |
| 10 | Jun-18 |
| 20 | Jul-18 |
| 15 | Jul-18 |
| 30 | Jul-18 |