Cross Tab, Concatenate, and Creating a Total Column and Row Help

Hello,

I am trying to create a pivot-like table, and am having quite a few issues.

I have a couple questions, and am hoping someone can help me design the table as I need.

I've gotten a pretty good ways, but have hit a wall.

I'm looking to create a table like below, but I need the following:

1. I would like the associate name to be concatenated with the their ID (A396271, for example), and the name is provided in my input document.

2. I would like to remove all Nulls, and the Data Cleansing tool isn't removing them as I assume because they are not strings.

3. I would like to a new row and a new column to total the columns and rows.

4. And finally, I would like to have all the information in a table format.

I know this is a lot, but I'm hoping someone can point me in the right direction.

Thank you.

Help

Accepted answers

echuong1

To add the totals, try using the Add Totals Crew macro. This will add row and column totals easily.

New Workflow1.yxmd

All comments

Qiu

@JacobGFMR
It should not be diffucult, but to help us, can you provide the sample input and output in data format, not a picture.

JacobGFMR

Here is a sample input.

Queue_ID	Queue_Name	Manager	Work Item	Item_Type	Sub_Type
A111111	John Doe	Smith	W123456-02APR21	WITHD	FULLPO
A111115	Jane Roe	Smith	W123457-02APR21	WITHD	PROCES
A111222	James Dee	Smith	W123458-02APR21	WITHD	FULLPO
A111333	John John	Rogers	W123459-02APR21	WITHD	SSSSSS

I need to only show Manager Smith's team's info, so I don't need Manager Rogers' team's info, and I would like to have a table showing something similar to the below.

Item_Type	Sub_Type	John Doe - A111111	Jane Roe - A111115	James Dee - A111222	Total
WITHD	FULLPO	1		1	2
WITHD	PROCES		1		1
Total		1	1	1	3

AngelosPachis

Maybe you can try this @JacobGFMR ?

example.yxmd

JacobGFMR

@AngelosPachis

Thank you for the provided example work flow.

I have used it and am very close to what I am needed, but I still have an issue.

For example, when I have the following information, I am not getting the type of output I want.

Provided is an example of my input, what I'm getting as my output, and what I am needing as my output.

INPUT

Queue_ID	Queue_Name	Manager	Work Item	Item_Type	Sub_Type
A111111	John Doe	Smith	W123456-04APR21	WITHD	FULLPO
A111111	John Doe	Smith	W123457-04APR21	WITHD	FULLPO
A111111	John Doe	Smith	W123458-04APR21	WITHD	FULLPO
A111111	John Doe	Smith	W123459-04APR21	WITHD	INSERV

OUTPUT

Item_Type	Sub_Type	John Doe - A111111	John Doe - A111111	John Doe - A111111	John Doe - A111111	Total
WITHD	FULLPO	1	1	1	[Null]	3
WITHD	INSERV	[Null]	[Null	[Null]	1	1
Total	Total	1	1	1	1	4

NEEDED OUTPUT

Item_Type	Sub_Type	John Doe - A111111	Total
WITHD	FULLPO	3	3
WITHD	INSERV	1	1
Total	Total	4	4

How can I get the output to do a sum of all Sub_Type for a given Queue_ID where it doesn't show a different column for each/same Sub_Type?

I need to know the total amount of the different Sub_Types for the given Queue_ID.

Please let me know if this makes sense.

Thank you.

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335