community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Key Value Pair Converted to an Array

Highlighted
Meteoroid

Hi there, I am desperately trying to convert fields (originally from a JSON) from a key value pair to an array. The below table illustrates 3 different events (Record 0, 1 & 2 with associated Event IDs). Each event has associated entities which are impacted by that event, seen in red. The entity ID is currently found in the 'Field Name' field while a '1' represents 'true' and a '0' represents 'false'. I would like the entityID numeral value to show up in the 'Value' field and the 'Field Name' to contain 'entityID' and a count of the number of entities within that record (same as location field). I am able to switch the fields around but battling to get a count on the entityID. Please help if you are able to!

 

RecordCurrent Field NameCurrent ValuePreferred Field NamePreferred Value
0EventId665EventId665
0creationDate2019-04-09creationDate2019-04-09
0locations.0UNITED STATESlocations.0UNITED STATES
0locations.1CHINAlocations.1CHINA
0locations.2UNITED KINGDOMlocations.2UNITED KINGDOM
0statusBeing processedstatusBeing processed
05961entityID.0596
05971entityID.1597
05981entityID.2598
1EventId684EventId684
1creationDate2019-04-10creationDate2019-04-10
1locations.0AUSTRALIAlocations.0AUSTRALIA
1locations.1CHINAlocations.1CHINA
1statusNewstatusNew
16631entityID.0663
16671entityID.1667
16681entityID.2668
2EventId688EventId688
2creationDate2019-04-10creationDate2019-04-10
2locations.0FRANCElocations.0FRANCE
2locations.1UNITED STATESlocations.1UNITED STATES
2locations.2UNITED KINGDOMlocations.2UNITED KINGDOM
2statusNewstatusNew
26981entityID.0698
26991entityID.1699
Alteryx Certified Partner

Hey @rebeccarlisle 

 

mutlirow formula is your friend here, you can get it incrementally increase the number depending on your desired criteria :) Here's an example:

 

Capture.PNG

Labels