Challenge:-
Using the Hollywood Stories data source(Click to download), instead of listing the genres that a studio produces as separate rows, combine them into a comma separated list as a single row as shown below.
The challenge is to get multiple rows of data into a single row.
Concept:-
To do this, we need to use a table calculation to take the previous genre and line them up. The function PREVIOUS_VALUE can be used to iteratively take the previous value and line it up with the current value.
Steps:-
1.Create a Calculated Field with name List with Comma and enter the following formula
This calculation is checking whether the index is 1(or) not.If it is 1,then first genre is listed.
In the picture below, since Drama is the first genre listed under 20th Century Fox, the genre Drama is returned
If the index of a genre is not equal to 1, then the calculation is referring to all other rows except first row, and will take the previous value,which would be the genre from the previous row, separated by a comma, followed by the current genre.
Drag and drop List with Comma calculated field to Text on the Marks card, our view will look like this:
This is computing using Table (Down) which refers to the all of the studios in the table.
But we want for each studio, so we want to change how it is being computed.
2.Right-click on List with Comma field on Marks card, and Select Compute Using and then Pane (Down).
3.Drag LeadStudio,Genre into Rows
4.Right Click on Genre and uncheck show header.
5.Create a Calculated Field "Filter", with the following formula:
This rank will take all the string values in List with Comma field and computes rank.
It means longest string will be ranked the highest.
6.Drop Filter in between Lead Studio,Genre and convert it to Discrete.
7.Right-click on Filter in the Rows shelf and select Compute Using > Pane (Down)
Now the RANK calculation will be computed based for every pane, which in this case, for every Lead Studio.
Now we can see that the row within the pane that has the longest string value is ranked as 1.
8.Drag Filter field onto the Filters shelf and only select 1.
This selection allows us to only show the line if the rank is 1.
Create Calculated Field "Color" &Drop onto the Color shelf,
so we can clearly see which studios produce movies of more than one genre.
Finally returns us a single comma separated line for each studio in our data set.
Thanks Guru's,
Prazval.ks
