Tricky table view

[Anonymous]

Hi!

I have three columns, ID,analysis and results:

ID, Analysis, results: 

A, x, 1

B, y, 2

C, z, 1 

D, x, 3

E, z, 2

F, y, 3

Customer wishes to show these values as:

         X                         Y                           Z

A                          1                           -                           -

B                          -                           2                           -

C                          -                           -                           1

D                          3                           -                           -

E                           -                           -                           2

F                           -                           3                           -

Is this possible to do in an easy way? I have several millions of IDs and hundreds of different analyses.

Many thanks! 

[m_woolf]

Looks like a pivottable

[adamdavi3s]

and here is an example of how to do it

[Anonymous]

It is a pivot...Are you unsure how to achieve that pivot view or how to control how you will display that many records horizontally and vertically?

[Anil_Babu_Samineni]

Create Pivot table and then use

Dimension --- ID, Analysis

Expression --- Sum(results)

Then, Transpose Analysis from Column to Row level. I remind you this should done by pivot object only

[Anonymous]

The amount is tricky, it's actually to large for my document to load without crashing. My best choice so far is to let it stay as a tablebox and then do the pivot in excel for the by-the-customer-selected choices

[Anonymous]

Add a listbox with analysis records and put a textbox notifying the user they need to view a maximum of 10 (The number is up to you but lets say 10 for now) analysis at once OR use the straight table and pivot in excel - You will have to make both Straigt & pivot available to the end user. Either on seperate tabs or using Show hide based on a object to allow them to switch between the two

put this condition on the pivot chart

=getselectedcount(analysis)<=10 (if on seperat tabs)

OR

vYourVariable = TheShowValueForPivot & getselectedcount(analysis)<=10 (if using show hide for the chart types)

This will only show a maximum analysis records of 10 or less and will be hidden when more are selected.

[Anonymous]

This should reduce\limit the load time and memory usage

[sunny_talwar]

If you want to do this in the script so that you can continue to use this as a table box, then may be this

Table:

LOAD * INLINE [

ID, Analysis, results

A, x, 1

B, y, 2

C, z, 1

D, x, 3

E, z, 2

F, y, 3

];

FinalTable:

LOAD DISTINCT ID

Resident Table;

FOR i = 1 to FieldValueCount('Analysis')

  LET vField = FieldValue('Analysis', $(i));

  Left Join (FinalTable)

  LOAD ID,

  results as [$(vField)]

  Resident Table

  Where Analysis = '$(vField)';

NEXT

DROP Table Table;

[Anonymous · Accepted solution]

See attached. I used one of the approaches above to limit the number of analysis viewable.

I limited to 2 due to using sample data. You can change the variable to 10 or any number you wish.

Both pivot (Limited to X viewable analysis) and straight will be available.

Hopefull that helps...