List box with Transpose

[jad0575]

Hi 

I'm trying to have an app where you choose the key fields and fields data in Transpose by List Box where it feeds in from an excel sheet! The key field worked as I've chosen only one column but the fields data is not working. In the debug mode, the value is changing but this is not reflected in the output of the transpose.

I believe I need to update the action tool with formula to be able to benefit from more than one input but I can't find out how that specific formula should look like!?

Thank you.

[afv2688]

Hello @jad0575 ,

The values wont change for that. I would recommend you to run previously another short app and chain it to the actual one just to create a file with the names of the inputs. Then on the listbox use the configuration to read from a file (the one created).

This would do it 🙂

Regards

[patrick_digan]

@jad0575 Attached is an example. The key is to put some complicated xml in the generate custom list section of the list box tool. Then you can use the action tool to update the xml of the transpose. I'm really just replicating the xml from the transpose tool. I've made it so both the key and data should be able to have multiple fields. @afv2688 was pointing out that when your excel file changes, the list box tool won't necessarily dynamically update itself.

[jad0575]

Hi Patrick,

@patrick_digan I couldn't open the attached because I don't have the most recent version and it takes ages to get approval to update. would you please post a photo of the XML formula of the transpose to tick the Data Fields? 

Many thanks in advance

[patrick_digan · Accepted solution]

@jad0575 What version are you running? I've attached a yxzp version 10.0 in case that helps. The dropdown tool that you want to update the data section of the transpose should use the generate custom list option with this text in each box:

start text:
<Field field="

separator:
" selected="True" /><Field field="

end text:
/><Field field="*Unknown" selected="False" />

Then you would use an action tool like so:

[jad0575]

Brilliant, thank you a lot! May I bother you to recommend a source to learn more about XML? is there one here in the community which is more aligned with Alteryx design?

[patrick_digan]

@jad0575 In the words of @AdamR_AYX "Alteryx is a fancy xml editor".

I don't know of any resources, but perhaps my thought process will help. If you haven't already, you'll want to go to options >> user settings >> edit user settings >> advanced >> check "display xml in properties window". Now I would start with the transpose, and we can see what the xml looks like when we have Field1 as a key and Field2 as the data: 

So we just need to build the data xml dynamically (<Field field="FieldName" selected="True" />) for all of the selected fieldnames. Just in case, I also set the unkwnown to false (<Field field="*Unknown" selected="False" />) so no other fields come through. I find the best way to build this xml is the listbox tool itself. I start with the start (<Field field=" ) and end text (" selected="True" /><Field field="*Unknown" selected="False" />). Notice that when it puts my selected field name in the middle, the 1 field case is good (<Field field="FieldName" selected="True" /><Field field="*Unknown" selected="False" />). Now if I have more than 1 field selected, I use the separator to finish the first field and start the second (" selected="True" /><Field field="). Then I just use the action tool to replace the transpose's current xml with my created xml.

[jad0575]

Massive thank you for this detailed information. I didn't know how to get there.