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

Transposing Data

anoopm7
5 - Atom

Good Afternoon !!

 

I have a SQL query that pulls data and there is two fields called chargeType and ChargeAmt. The charge could be none or multiple values depending on the order. For example

 

Data looks like this

 

Ord#    ChargeType#  ChargeAmt#

1896    A-123             $89.69

1896    B-589             $239.69

2026    K-789             $123.56

 

I would want to convert it to as so that there won't be a unique row for an ord#

 

Ord#  Chargetyp1 ChargeAmt1  Chargetyp1  ChargeAmt1  ChargeType3 ChargeAmt3

1896  A-123           $89.69          B-589           $239.69          NULL              0

2026  NULL           0                   NULL           0                      K-789             $123.56 

 

What would be the best way to do it.

 

 

4 REPLIES 4
tcroberts
12 - Quasar

Sorry can you clarify your desired output a little? I'm unsure what exactly you're looking for, as you've lost the 2026 Order#, and your second row in the desired output appears to have all 3 charges.

 

Thanks!

anoopm7
5 - Atom

My apologies. I corrected the way I would like to see the data.

tcroberts
12 - Quasar

Thanks,

 

So just to clarify, you'd like each Charge type number to have its own column? i.e. A transaction using A-123 will never be in the same column as K-789?

 

Or are you looking to generate additional columns for every transaction, with the total number of columns proportional to the ID which has the most transactions associated with it?

 

In this second case, You'd want the ChargeType1 and ChargeAmt1 to be K-789 and 123.56 respectively, with ChargeType/Amt2 and ChargeType/Amt3 being Null(), correct?

 

I'm working on getting an example up, but if you could clarify this last thing it would help,

 

Thanks

 

EDIT: Here's a screenshot of what something like the first case would look like, which is what it appears to me that your desired output is. It appears that each ChargeType column would only ever have values with the same ChargeType, so instead I've make these ChargeTypes the headers. That being said, this would fail if there were more than 1 transaction of each type for a given ID. The way I've currently got it set up is that it would sum those, and I doubt that is what you'd be looking for.

Spoiler
transposetouniqueid.PNG

 

 

 

anoopm7
5 - Atom

Yes, I wanted to generate columns for every transaction with the total number of columns proportional to the ID. So for some orders few columns could be empty as they might not have values.

 

You'd want the ChargeType1 and ChargeAmt1 to be K-789 and 123.56 respectively, with ChargeType/Amt2 and ChargeType/Amt3 being Null(), correct? ---> Yes, since Ord#2026 does not have any values associated with ChargeType1 and ChargeType2.

 

I am excited !!

Labels