"Unpivoting" Data
Forums
I have imported into IDEA an excel file with about 100 columns. The first row in each column is a reporting entity, then the remaining rows are all the allowable values for that reporting entity. When importing this file into IDEA, all reporting entities become field names and the allowable values are all the values in that field.
I also have another database with two fields that I am trying to ensure that only valid combinations of reporting entities and allowable values are used. One field could have any possible reporting entity, and the other should only have an allowable value for that reporting entity.
I would be open to other methodologies, but my plan is to try to "unpivot" the data from the database with about 100 columns by converting it to a 2 column database that contains all possible combinations of reporting entities and allowable values. Once I have that, I can use the join function to compare the unpivoted data to the original 2 column database to ensure all values are allowable. My ask is for help in developing a script to unpivot the data.
Hello,I have put together a…
Hello,
I have put together a script for you to test out and hopefully it does what you need. Let me know if it doesn't or if you have any problems.
https://ideascripting.com/node/8609
Thanks
Brian