community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Convert to crosstab from raw data

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.

 

 

 

 

Sample.PNGtable1 is source, table 2 is target

Alteryx Certified Partner
Alteryx Certified Partner

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

 

 

Bolide
Bolide

Hello @MichaelJack

 

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

 

Treyson

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.

Alteryx Partner

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

 

Cheers!

Highlighted
Alteryx Partner

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

Alteryx Partner
 
Meteoroid

Hi Andy,

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


Regards,
M.

Bolide
Bolide

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

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