Cross Tab help

[StephenT]

Greeting all

Apologies for the vagueness of this request

I run a weekly asset report that currently lists all assets allocated to staff members in each row (see below), and send those staff members an email to remind them to return unused assets.

Currently my report lists each asset on a seperate row (see below)

Staff name	Serial #	Primary Asset	Asset Status	Type/Model	SIM #	SIM Status
John Smith	PF1P8YFN	Yes	Spare	Laptop	123456789	Deactivated
John Smith	PC0NUMAC	No	Spare	Laptop	9876564321	Not listed
John Smith	PC0S50PG	No	Spare	Laptop	1472583969	Not listed

I have played around with the CrossTab tool as I want to have the additional laptops show in the one row for each staff member, so they appear as extra columns and not affecting the other 20+ columns or data in the report

Needs to show as (if possible)

Staff name

Serial # 1

Primary Asset 1

Asset Status 1

Type/Model 1

SIM # 1

SIM Status 1

Serial # 2

Primary Asset 2

Asset Status 2

Type/Model 2

SIM 2 #

SIM Status 2

Serial # 3

Primary Asset 3

Asset Status 3

Type/Model 3

SIM # 3

SIM Status 3

John Smith

PF1P8YFN

Yes

Spare

Laptop

123456789

Deactivated

PC0NUMAC

No

Spare

Laptop

9876564321

Not listed

PC0S50PG

No

Spare

Laptop

1472583969

Not listed

Is this possible in Alteryx 2019.3.5.17947 ?

[grazitti_sapna]

Hi @StephenT , please refer to the screenshot and workflow.

Thanks.

[bhrmitra]

Hi  StephenT,

You can achieve it using transpose, crosstab and record ID tool properly.

If this solves your issue please mark the answer as correct and also hit the like button, if not let me know! I've attached my workflow for you to download if needed.

Thanks,

Abhra Mitra

[StephenT]

Apologies for not responding sooner, but took a week's leave .

I have tried the both of the above solutions on my data files, and found bhrmitra's works better for me, giving me the result I need

When running the workflow on my data file i get a warning "Warning: Dynamic Rename (7): The number of rows in the Source do not match the number of renames."

I also found that when i run the workflow for 2 people, with multiple laptops assigned (1 staff member with 9 laptops, 1 staff with 9 laptops) the workflow created the correct number of columns (9 assets x 7 columns for each asset) for the first staff member, then adds another 63 columns (9 assets x 7 columns) per the attached XLSX file.

Have I done something wrong, when configuring the workflow

[StephenT]

sorry, I forgot to attach a 2nd file, with how I would like the results to look.

[bhrmitra]

Hi StephenT,

Can you provide us with the exact input data so that the scenario can be replicated?

Thanks,

Abhra Mitra

[StephenT]

Here is the data file

[bhrmitra]

Hi StephenT,

Check if the attached solution works for you. You have to actually group by the User name or staff name in the "Crosstab" and "Transpose" tools.

Thanks,

Abhra Mitra

[StephenT]

Thanks Abhra

When I run your workflow it still adds extra columns for each user.

I've had a look at the crosstab and transpose tools and can't work out how to group. I also compared the first workflow you sent last week, against today's and they look the same.

Sorry, still a learner as far as Alteryx goes.

Cheers

Stephen

[grazitti_sapna]

Hi @StephenT , I guess this is what you are looking for if this solves your purpose please mark this post as solution.

Thanks.