Transposing json output

[AJuliet]

Hi All,

I need help transposing data which is an output after parsing json format. I have two columns column A and B containing values. Column A contains header and Column B contains values. When I am using cross tab to transpose the data, it can either transpose first/last or concatenate the values which can be lengthy to delimit and process. 

I am able to use the regex to parse json output and using formula tool renamed the column headers in column A but stuck with transpose. If it can be done for one set I can implement the same for other set of data as I have multiple country code. attached the input and expected output in excel file.

[grazitti_sapna]

Hi @AJuliet , You can achieve it by using cross tab but you need to use some preparation tools as well with it. Please find the workflow and also I have added 2 cases in the input instead of one to make it more clear. Please accept my solution if it helped. Or let me know if you need any additional support.

Thanks!!

[AJuliet]

Hi @grazitti_sapna. Thank you so much for the help and I am able to achieve 98% of the result as expected. There is one catch that one of the CountryCode doesn't have the date and values but Year when generating the ID based on Date. it is assigning the same ID because it couldn't differentiate. Attaching the file with the sample case. When you'll run it you'll see the issue.

Thanks again.

AJuliet.

[grazitti_sapna]

@AJuliet , The json response has to be consistent for all Country Codes when we are getting the response. Are you getting the response in the same way you are sharing the excel or is it different and the output that you are sharing is created by you after transformation. Please share the original json so that I can provide you better solution.

[AJuliet]

@grazitti_sapna, I have attached the sample which I am parsing using json parse and later I am using regex (syntax - .*\.(.*..*$) ) to pick the value from right  side of the column and renaming them in Formula tool, so no alteration in Data(just changed the original values the demo value).

I am filtering out "periodResolution" date and year and taking "dateOptions": date and year (where there is no date for this CountryCode EF butt dates are available for others). You WF is working fine when merging with my WF but the only logic applied on date to generate ID on date is concatenating the Year values as there are no Dates.

[grazitti_sapna · Accepted solution]

@AJuliet , Please find the updated workflow. Let me know if it solves your problem and I can see in EF CountryCode case we dont have values in Date and Value columns. So if you want data in any of the fields then please let me know what it should be.

Thanks!!

[AJuliet]

Thank you so much, it fixed all the glitches and I don't need to populate any values in Date and Values columns. Hopefully when they'll appear in the data I just have to remove the text-to-column tool and it'll work fine.

Thanks again for the help.

Regards,

AJuliet.

[AJuliet]

Hi @grazitti_sapna , I have new scenario where now we have another KPICode for same CountryCode, so the recordID is same for both the KPICode against the CountryCode and when it is transposing the data it is concatenating the values based on RecordID based on comma separator. I Though of applying another multi-row but id doesn't look good to me. Let me know your thoughts. Attaching the sample file with updated case.

Solution: I used text to column to split the values by comma separated in the KPICode which has multiplied the rows for each KPI. Let me know if this is the right approach.