Do elements exist in a hierarchy ?
- Sep. 27, 2022
Introduction
Let's say that you have a dimension or hierarchy and you want to test whether a bunch of elements exist or not. Furthermore, which elements exist and which elements don't ? In the good old TM1 Architect Subset Editor, you paste the elements from the clipboard and TM1 will gladly inform you what elements don't exist, using a message box. In the Set Editor in Planning Analytics, you can also paste and a box will again inform you.
Examples
In Architect: you would go to the respective dimension (notice that hierarchies are not known in Architect). First copy the elements to be evaluated and paste in an empty area of the Subset Editor. Then hit Ctrl + c to copy the contents of the resulting message box. Paste in Excel.
This gives you an output that is not always easy to manipulate. But WAIT... with a few nifty formulas, you can extract what you need - the list of non-existing elements:
=SORT( DROP( UNIQUE( TRIM( TEXTSPLIT( A4, , {":";", "} ))), 1 ))
The blue border around cells A10:A14 shows the dynamic array and auto-expanding output. Now 5 rows, it could be more or less and Excel will adjust. The only thing to watch out for is when the non-existing elements would contain a comma or colon: these are the separators on which we split the text string.
In PAW, it's easier. You can copy the non-existing elements from the box and paste straight in Excel:
