How to transpose data

sagarkpr

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?

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

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

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

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