How to transpose data

sagarkpr
sagarkpr
in Modeling

I have a module A - Dimension Class and Subject

In the module, I have a line item (list formatted) called Order.

User will use this module to define Order for each Subject for each class.

eg. For Class X; SubA - Order 1 , SubB-Order2 etc.

Now I want to create another module dimensions by Class and Order, That can show that For Class X and Order 1 - SubA

Essentially Transpose the information from one module to another.


How can I achieve this?

Tagged:

Answers

  • derektslim
    derektslim

    Hello sagarkpr

    You could consider creating a module that combines all three dimensions (Class x Subject x Order) to contain all the information, then transform it back to the dimension you require. My solution may not be the most efficient, especially if you have large number of list items in all three lists. Do set up the SYSTEM modules as well for best practices (ITEM, NAME ITEM, etc.)

    Class x Subject : Random data

    image.png

    Class x Subject x Order : Combine all three dimensions using Text

    image.png

    IF ITEM(Order) = Class x Subject.Order THEN NAME(ITEM(Subject)) ELSE BLANK

    Class x Order : Transposing it into another dimension combining the subjects together

    image.png

    TEXTLIST(Class x Subject x Order.Subject TXT, "|", Subject)

    This handles cases where there are more than 1 of the same order for different Class x Subject combination through TEXTLIST function. However, if you will require only 1, you may consider tweaking the whole formula around to utilise the Summary functions instead.

    Hope this helps!

    Derek Lim

  • Dikshant
    Dikshant

    Derek solution is good. But consider entering the data in a cross-section module for sake of simplicity.

    Give all combinations of class, subject, & order and a boolean line item, and select all applicable combinations.

Categories