- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Presenting Data in Table
I want to display below table into this format, without using CrossTable as it may effect other data.
From:
Name | Category | Address | Code | Details |
ABC | First | 1234 ABC | 898989 | QWERTY |
To:
Name | ABC |
Category | First |
Address | 1234 ABC |
Code | 898989 |
Details | QWERTY |
Please Accepts as Solution if it solves your query.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
So... the data on your dataset are structured as this:
Name | Category | Address | Code | Details |
ABC | First | 1234 ABC | 898989 | QWERTY |
And without changing the dataset structure you want to visualize in your straight table this:
Name | ABC |
Category | First |
Address | 1234 ABC |
Code | 898989 |
Details | QWERTY |
right?
If a post helps to resolve your issue, please accept it as a Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes
Please Accepts as Solution if it solves your query.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
As Below;
temp:
LOAD * Inline [
Name,Category,Address,Code,Details
ABC,First,1234 ABC,898989,QWERTY
DEF,Second,5678 DEF,121212,XYXYXY
];
Use a Pivot Table:
Row Dimension
=ValueList('Name','Category','Address','Code','Details')
Column Dimension = [Name] <<-- or any other ID field that identifies each row uniquely
MEasure
if(ValueList('Name','Category','Address','Code','Details')='Name',Name
,if(ValueList('Name','Category','Address','Code','Details')='Category',Category
,if(ValueList('Name','Category','Address','Code','Details')='Address',Address
,if(ValueList('Name','Category','Address','Code','Details')='Code',Code
,if(ValueList('Name','Category','Address','Code','Details')='Details',Details
)))))
Sorting: By Expression = ValueList('Name','Category','Address','Code','Details')
/// on Valuelist dimension
If a post helps to resolve your issue, please accept it as a Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, How can I make it work if one field has more than one value, in that case it is showing null in pivot table but showing values in straight table.
Please Accepts as Solution if it solves your query.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Use concat()
Example
if(ValueList('Name','Category','Address','Code','Details')='Category',concat(distinct Category,',')
Also , kindly close the thread by accepting a response as a solution
If a post helps to resolve your issue, please accept it as a Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@vinieme12 Thank you for the solutions you have provided above. I am also trying to create something similar without having to use CrossTable.
If I want to create 2 value columns (unrelated) instead of one, can this be done by extending your above logic?
For instance I am trying to achieve the following output where each numeric value is a different column in my dataset and the table at any given time represents a particular company which the user can select from a filter.
Dataset:
cash original | cash new | debt original | debt new | ebitda original | ebitda new | |
Company 1 | 100 | 200 | 50 | 120 | 30 | 190 |
Company 2 | 200 | 400 | 100 | 240 | 60 | 380 |
Company 3 | 400 | 800 | 200 | 480 | 120 | 760 |
Desired Output for one company:
Original value | New value | |
Cash | 100 | 200 |
Debt | 50 | 120 |
Ebidta | 30 | 190 |
By using your suggestion above, I was able to get the labels and one of the columns. How do I go about getting the second column as well to the right?
Attached is the sample dataset for reference.
Thanks!