Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Convert to crosstab from raw data

MichaelJack
6 - Meteoroid

Hi Guys,

I have a sample of dataset that i want to create a corsstab to count the number of customer with specific condition like:
- No.of.Customer buy something on 2018-06-02 and have stage=1 & Status=Y

- No.of.Customer buy something on 2018-06-02 and have stage=1 & Status=N

- No.of.Customer buy something on 2018-06-02 and have stage=2 & Status=Y

- No.of.Customer buy something on 2018-06-02 and have stage=2 & Status=N

...

- Total of Quantity by date.

please prefer to the image below for detail, i tried to use crosstab & transpose from Transform tab but it seems like to unable to do this. Please help to advice me the solution!

 

Thank you so much!.
Regards,
M.

 

 

 

 

table1 is source, table 2 is targettable1 is source, table 2 is target

9 REPLIES 9
BenMoss
ACE Emeritus
ACE Emeritus

The detail from between the first statement and the ...'s which I assume means all possible cases can be generated using a single summerize tool.

 

GROUPBY 'Date', 'Stage' and 'Status', and SUM your 'Quantity'

 

You can then cross-tab this into the format you like  and one additional option then is to create a total column.

 

If you attach the sample data shown in your post then I can build out an example for you.

 

Ben

 

 

Treyson
13 - Pulsar
13 - Pulsar

Hello @MichaelJack

 

You will first need to summarize before the Cross Tab. Please see the attached.

 

Treyson

Treyson Marks
Senior Analytics Engineer
MichaelJack
6 - Meteoroid

@Treyson:

Thank for your response, the formula should need to be fixed a bit, the no.Customer(Stage=1 && Status=Y) means we need to count CustomerID follow the condition. But the Quantiy = SUM(Quantity) over all of them.
It appreciated if you can help to modify the file and i think the result will be perfect!

 

Thank in advanced.
Regards,
M.

AndyMoncla
10 - Fireball

This may not be exactly what you want but it might help.

 

Cheers!

AndyMoncla
10 - Fireball

By the way, I misread your request.  I thought you were looking for the customer ID under each category.  Sorry.

AndyMoncla
10 - Fireball
 
MichaelJack
6 - Meteoroid

Hi Andy,

thank for you help! but still loss the SUM of quantity.


Regards,
M.

Treyson
13 - Pulsar
13 - Pulsar

The sum tool is pretty rad and I want you to know how to use it in the future so let's learn how to fish!

 

Below is a picture of the configurations panel for the Sum tool, if you look at the Fields area, there is a list of all fields that are coming into the tool. The "Actions" area is where the magic happens in the tool. if you select a field from the list above (clicking on the field highlights and "selects" it), there is a drop down option in the actions pane that allows you to select what functionality the sum tool is performing. Since we have already decided to group on "OrderDate", "Stage" and "Status" the sum tool is going to bring us to whatever aggregation we are asking to to do on those distinct values. 

 

Since you said you wanted to do a count of the customer IDs, selecting that field and then through the dropdown selecting count (or Count Distinct) will adjust what we are aggregating on. If you need to remove a field that already exists within the tool, there is a button on the side of the actions pane that is a circle with a "-" within. Just click that while selecting whatever field you want to remove. Since the name of the aggregated field is changing, this will throw an error message on the "cross tab" tool. You will have to adjust that to sum on our new "count_CustID" field.

 

Let me know if this needs any further clarification. :)

 

 

 

SUm Tool.png

Treyson Marks
Senior Analytics Engineer
MichaelJack
6 - Meteoroid

Thanks Treyson,
I am practicing on that and let you know when i completed, it will take time because im new in Alteryx haha.
thank for your help.

regards,
M.

Labels