Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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...
Looks like a pivottable
and here is an example of how to do it
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?
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
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
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.
This should reduce\limit the load time and memory usage
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;
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...