Hello,
I am stuck in a predicament.
I have correlation data in table format which needs to be converted to a JSON with a particular structure, dynamically, as the table format (ie. the column names and the values) change depending on the customer.
Input format :
Output format:
I find no approach which will help me out in this.
test_heatmap-to-json.knwf (19.8 KB)
PS. @ipazin , just putting you on here because i have seen how active you are and i believe you can help.
Thank you.
I’ve tried using various methods, but I am not able to come up with a logic to make this dynamically.
Can someone with expertise in this tool, please reach out for guidance. It would be much appreciated.
Thank you
Hi @parthak,
Just to understand your problem (and if I might be able to help 
)
So your problem is not creating the Json file, but how to turn the process to work the same way even if the columns/column names change?
Hi @parthak,
not sure I can help or suggest more than I did in this topic:
Please don’t open multiple topics for same issue.
Br,
Ivan
Hi, @AnotherFraudUser,
Yes, I need to create a JSON file from that input data, but the input changes depending on the customer. The input is always going to be in the same format though. Please let me know if you figure something out.
@ipazin sorry about reposting. I was just trying to get some help
Hi @parthak,
i looked into your workflow (which is currently just the Excel file?) and your desired output format.
I actually never used any JSON nodes before - so here maybe a solution without any JSON nodes whatsoever (not complete just until the first submetrics part is calculated.
And not filtered to the exact KPIs you want to show in the JSON
The basic idea is - if you do not know how to create the layout through the json nodes - just create the correct layout through string manipulation and string concatenating
Very very likely there is a way simpler solution if you look into the JSON nodes or checking @ipazin suggestions in the other thread.
However this brute-force solution is a sure way to get a result - as far as I can see
*please do not create new threads for the same problem - while the old threat is still open.
And if you split a topic, I guess it would be nice to just note that there was previous information in another thread!
(@ipazin now closed the old thread - but basically asking for the same questions twice or giving already suggested solutions twice just wastes time 
)
…and finally liking the responses from people to your questions helps to motivate people (noticed that ipazin did not receive any from you in the previous thread 
For people spending their time here to help you - this is the least you could do 
Gotta collect those profile banners 
KNIME_project21.knwf (43.3 KB)
*as well maybe the suggestion, maybe try to start with a smaller version of your json file e.g. just one submetrics part on the lower level and after you successfully created that - start to work up your levels.
I think either way (using the json node or another workaround) due to the complexity of your json file - you should start small and finally if the smaller parts work - go up to the final json
Good luck 
Hi @parthak,
did you manage to create your json file?
I am still struggling with it. I used your method and it works for segregating the metrics and the submetrics but the problem is that I am not able to get it to be nested.
As in, the submetrics related to the metrics have to be nested under the respective metrics.
Hi @parthak,
could you post your current try as well as the example Excel (as the file was reference to a directory on your pc it was kind of hard to test the workflow) ?
Will try to more or less create/enhance the workflow tomorrow
So you have a whole working template with all the nested parts
heatmapdata.xlsx (22.2 KB) KNIME_project21_test.knwf 2.knwf (446.2 KB)
i have attached the data that the current workflow above.
https://jsonblob.com/67e435c1-af38-11ea-8ded-bd5e632e6f30
you can use this link to access the nested JSON output, which i need
Hi @parthak,
attached a complete working workflow to get to a shortend version of your json:
https://jsonblob.com/2cc29296-b588-11ea-9bad-bbe3eeac2e48
I removed many of the KPIs to have less assignments in the KPI groups.
However I kept at least 2 KPIs on each level - so it should work as well for your complete JSON.
->you will have to fill in the assignment tables in the beginning.
As I could not see any logic how the assignment works this was the easiest solution
…if there is a generic logic behind the metrics/submetrics groups then you can just put it at the beginning insead 
For metrics:
For submetrics:
Workflow:
JSON.knar (106.3 KB)
Workflow result:
https://jsonblob.com/6f44f74f-b590-11ea-9bad-c3330e522ce3.
{
“guid”: “D2676968-C552-4319-A0F9-22ACF007E441”,
“primaryprogram”: “Home Depot - ALL”,
“metricdata”: [
{
“name”: “AHT”,
“data”: {
“metrics”: {
“Conformance %”: 0.09311737894674196,
“Email Capture Rate %”: 0.12770707068957,
“RPH”: -0.25425068053486416,
“Reopen%”: 0.16077846713450503,
“VOC ASAT”: 0.03156019845823264,
“VOC LTSA”: 0.1122460525270463
},
“submetrics”: [
{
“Call Answered_AHT”: {
“Scheduled Lunch Time_Conformance %”: -0.16108320714285218,
“Actual Break Time_Conformance %”: 0.20435632484264316,
“Actual Lunch Time_Conformance %”: -0.1085923444453887,
“Adjusted Staff Time_RPH”: 0.2374171783419338,
“Admin Time_RPH”: 0.03445869768550578,
“Email Capture Count_Email Capture Rate %”: 0.10760800855315494,
“Resolved Count_Email Capture Rate %”: 0.2757286226494401,
“Resolved Count_Reopen%”: 0.017419158081697937,
“Scheduled Break Time_Conformance %”: 0.17965042978139703
}
},
{
“Hold Time_AHT”: {
“Scheduled Lunch Time_Conformance %”: -0.22871984040114765,
“Actual Break Time_Conformance %”: 0.17913899996257812,
“Actual Lunch Time_Conformance %”: -0.17079030433769818,
“Adjusted Staff Time_RPH”: 0.5299197830861845,
“Admin Time_RPH”: 0.19134211310101645,
“Email Capture Count_Email Capture Rate %”: -0.020030599561981055,
“Resolved Count_Email Capture Rate %”: 0.08609664692934205,
“Resolved Count_Reopen%”: -0.05932185501366315,
“Scheduled Break Time_Conformance %”: 0.1858460937078456,
“VOC Count_VOC ASAT”: -0.11679712367060496,
“VOC Count_VOC LTSA”: -0.1009727765120373,
“Acw Time_AHT”: 0.5508943030539666
}
},
{
“Talk Time_AHT”: {
“Scheduled Lunch Time_Conformance %”: -0.13032676481403863,
“Actual Break Time_Conformance %”: 0.18329182376880118,
“Actual Lunch Time_Conformance %”: -0.0665169565184278,
“Adjusted Staff Time_RPH”: 0.5194391432061878,
“Admin Time_RPH”: 0.12830897871262298,
“Email Capture Count_Email Capture Rate %”: 0.06685008407380622,
“Resolved Count_Email Capture Rate %”: 0.1666225803376203,
“Resolved Count_Reopen%”: 0.0560893012033043,
“Scheduled Break Time_Conformance %”: 0.20826141975768164,
“VOC Count_VOC ASAT”: -0.08961134247866595
}
}
]
}
},
{
“name”: “VOC”,
“data”: {
“metrics”: {
“Conformance %”: 0.01549214657663076,
“Email Capture Rate %”: -0.12961751625624762,
“RPH”: -0.06868461050195497,
“Reopen%”: -0.061547158552090885,
“VOC ASAT”: 0.4196360964376155,
“VOC LTSA”: 0.4239676419893166,
“AHT”: -0.005992558084068192,
“Email Capture Rate”: -0.03266237102811549,
“FCR”: 0.32031056574631495,
“ICP”: -0.218345139721597,
“VOC”: 1.0,
“ICP %”: 0.1367470251832892
},
“submetrics”: [
{
“VOC Count_VOC”: {
“Scheduled Lunch Time_Conformance %”: 0.24719049629245335,
“Actual Break Time_Conformance %”: 0.2763110275518443,
“Actual Lunch Time_Conformance %”: 0.22972470813838147,
“Adjusted Staff Time_RPH”: 0.4750700432415697,
“Admin Time_RPH”: 0.08086776182481961,
“Email Capture Count_Email Capture Rate %”: 0.586714447110612,
“Resolved Count_Email Capture Rate %”: 0.46606848379601884,
“Resolved Count_Reopen%”: -0.03198533316078207,
“Scheduled Break Time_Conformance %”: 0.3366338663555732
}
},
{
“VOC Sum_VOC”: {
“Scheduled Lunch Time_Conformance %”: 0.2033689987122358,
“Actual Break Time_Conformance %”: 0.2605964154972302,
“Actual Lunch Time_Conformance %”: 0.19934415618044043,
“Adjusted Staff Time_RPH”: 0.5133219207151379,
“Admin Time_RPH”: 0.11267196462586741,
“Email Capture Count_Email Capture Rate %”: 0.5430782838999504,
“Resolved Count_Email Capture Rate %”: 0.43941490400523237,
“Resolved Count_Reopen%”: -0.03613520703793213,
“Scheduled Break Time_Conformance %”: 0.315738911706042
}
}
]
}
}
]
}
Would still say that most likely the whole json group creation can be replaced by normal json nodes instead of this string only solution - but it works as far as I can see 
Hi @parthak,
did you manage to solve your problem now? 
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.
