Dynamic CROSSTAB columns with newly added column

Hi,

I need to add columns to my crosstab dynamically.. I feel like i always have to update it based on my inputs i load.

I would like to add the newly added columns without having to change them here.. I want to leave "Name" and "value" unchecked.

Ie if I load columns 202203, 202204 and so on it should automatially be picked up by the cross tab and not need alteryx.

any help would be appreciated.

Custom Tools

Accepted answers

All comments

grazitti_sapna

Hey @wonka1234 , you will have to create a macro to do so. Please check the below post in which they have modified the XML to change the group by columns in summarize tool. I assume you can use the same approach for cross tab as well.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/dynamically-select-ONLY-group-by-columns-using-macro/td-p/627278

wonka1234

@grazitti_sapna

thanks. Sigh this seems hard.

I have a text input, say 202202, 202203 . I am having trouble dynamically taking these inputs to update the xml as each time it will be different.

n8rushton

Would you please be able to share a dataset? I think this is possible with just a dynamic rename and a record

wonka1234

@n8rushton

File_Name	File_Date	202202	202203
Alteryx	202202	Y
Alteryx	202202	Y
Alteryx	202202	Y
Alteryx	202202	Y
Power BI	202202	Y
Power BI	202202	Y
Power BI	202202	Y
Power BI	202203		Y
Power BI	202203		Y
Power BI	202203		Y

so this is a sample input to my crosstab.. but 202203 isnt picked up automatically..

n8rushton

do the number of fields change?

Felipe_Ribeir0

Hi @wonka1234

Try this

Dynamic CROSSTAB columns with newly added column.yxzp

wonka1234

@Felipe_Ribeir0 getting a few errors at the macro .

Error: mc (175): The Action "Tool #6" has an error: ParseError: unterminated start tag 'Field' at line 2 and column 1
while processing "Action_UpdateRawXml"

ErrorLink: mc (175): https://community.alteryx.com/t5/*/*/ta-p/710269?utm_source=designer&utm_medium=resultsgrid|Record #1: Tool #4: The field "202003" is missing. Compare the tool configuration with the input stream.

Error: mc (175): The output connection "Output9" was not valid

my summarize tool looks to be okay:

and my input to my macro seems to be okay..

Felipe_Ribeir0

@wonka1234 try this version,

but you gonna need to adapt to your dataset, try running the macro with the data inside of it.

With this filter you can control the columns of the group by.

Dynamic CROSSTAB columns with newly added column.yxzp

wonka1234

@Felipe_Ribeir0

i think previous way was better as it selects everythin but name and value. I h ave more then just 202202 and 202203 that needs to be groups by.. but still getting the error when i include everything but name and value.

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335