Data360 Analyze

Is it possible to use the string value from a field as an input in the Pivot-node instead of writing column names manually?  

Here's the pivotcol-column containing a string with field names:

And this is what I'd would like to do:

This fails because Dataverse tries to find column pivotcol from the original data, not the column names specified in the string. Any solution to this?

Dear Ilkka,

Could you give me a little more information about your goals?  While I'm afraid that the exact feature you are asking for is not available, Dataverse does have quite a bit of functionality.  I'm thinking we might be able to achieve your goals through a different approach.

Thank you,

Rocco

Hi Rocco

I have 2 datasets retrieved via API in JSON format. Dataset1 contains actual data and Dataset2 contains column properties for Dataset1.

My goal is to pivot numeric columns in Dataset1 which can be identified through Dataset2.

JSON-node creates a wide table with many columns which is not optimal for analysis in Tableau. I do not want to do manual pivoting in Tableau each time I decide to visualize different dataset.

My ultimate goal is to use the Tableau Web Data Connector for selecting a table using Data Flow parameters and let Dataverse to fetch the data via API and prepare it for further analysis in Tableau.

Web Data Connector works but I don't know how to set up the data flow parameters and create dynamic pivoting flow described above.

If you are interested, I can share further details privately. 

Dear Ilkka,

Thanks for the detailed answer.  Taking a look at the data appears to be the best next step.  I have sent you a separate e-mail with some directions about how to best get me the files.

Looking forward to receiving the data,

Rocco

Hi Rocco

I noticed that Pivot-node accepts wildcards so I renamed all numerical fields to num_1, num_2, num_3... and specified PivotFields as 'num_*' in the Pivot-node. This solves the dynamic input issue. 

By using X-Ref and Transform, the pivoted field with column names is updated back to original. 

Here's a screenshot of the data flow:

BTW, I got the Data Flow Parameters working as well in the Web Data Connector. I just had to include 'wdc' in the Data Flow propery name :)

I'll contact you in case I have further questions. Thanks for your help. 

Dear Illka,

Glad to hear you found a solution.  I had a suspicion that the Change Metadata node along with the wildcards in Pivot would have been the solution here so I'm glad that you worked it out.

Feel free to make a new post if you have any questions.

Rocco