Summarize without knowing all the fields

[NikSTHF]

Hi folks,

So I have a bit of a problem I can't seem to solve. I have a data source that comes into Alteryx, I can prep most of it... however I'm not always going to know the field names (The data source changes depending on usage through a previous day)

Day one;

Agent Name	Time Signed In	Time Signed out	Status 1	Status 2	Status 3	Status 4
Dave	xxxx	xxxx	xxx	xxx	xxx	xxx
Dave	yyyy	yyyy	yyy	yyy	yyy	yyy
Brian	xxxx	xxxx	xxx	xxx	xxx	xxx

Day two;

Agent Name	Time Signed In	Time Signed out	Status 1	Status 2	Status 4
Dave	xxxx	xxxx	xxx	xxx	xxx
Dave	yyyy	yyyy	yyy	yyy	yyy
Brian	xxxx	xxxx	xxx	xxx	xxx

So the Summarize function is grouping based on Agent name, but sometimes the status fields aren't always in the data source and Alteryx throws a fit when they aren't included.

How on Earth am I going to get around this as I cannot figure it out?

[andre347]

What do you want to find out? You can use the transpose tool to pivot the Status Fields (to something like in the image below) and then use the summarize tool. But that depends on what you want to achieve. Do you want to count the number of statuses per Agent Name?

[NikSTHF]

Collate all the statuses per agent.

Sometimes there will be multiple sessions per agent and then the report would show these as individual lines. The statuses would need to be totalled and the Mimimum shown for "signed in" and the maximum shown for "signed out"

[andre347]

and make sure you tick the box 'Dynamic or Unknown Fields' in the Transpose tool. This will make sure that if new status fields come in they are picked up

[NickC · Accepted solution]

Hello,

This can be achieved by using the transpose tool before the summarize.  You will then need to cross tab to get the data back into the format that you want it.

Workflow attached V11.3

[andre347]

So in that case you'd want to use the summarize tool after the transpose tool and group by Agent, Signed In, Signed Out and Sum the Value.

[andre347]

@NikSTHF did you manage to solve this issue with the posts from @NickC and myself?

[NikSTHF]

Bobs your uncle. That worked perfectly.

Thanks for your help with this.