Dynamic Account number and value question (in Crosstab)

[Bren312]

Hello, this is my first post on these boards and I'm just learning Alteryx so...go easy on me, guys.  I'm using the Crosstab function to shift up to 8 account numbers from displaying in Rows to displaying as Columns, with the value for each account shown in the row under the account number.  I'm then adding each of these totals by line to get a P.O. total.

Example:

P.O.   Acct   Total

1       55       $5

1       66       $5

1       77       $5

Should show as

P.O.   Acct. 55   Acct. 66   Acct. 77   Total

1         $5           $5           $5           $15

But if one of those accounts had no activity, I'd get this

P.O.   -Null-    Acct. 66   Acct. 77   Total

1                    $5           $5           $10

The math is still correct, but I'd like to have all the possible accounts listed as column headers, even if they have no activity.  I tried playing around with Text Input or Input Data (using a Dynamic Rename) but couldn't figure it out.  Anyone have any ideas?  Thanks guys!

Bren

[jarrod]

you can try inserting all possible account numbers into a text field either as headers or as values to crosstab. See the attached workflow.

[Bren312]

Hi Jarrod, thanks for the advice; I really appreciate it.  I tried opening your workflow to check it out but it looks like it was created in a more recent version of Alteryx than I'm running so it won't let me open it.  Could you give me a Cliff's notes?  Sorry to be a PITA.  

[jarrod]

Can you try opening the file in notepad (or notepad++ is what i prefer) and change the line at the top:

<AlteryxDocument yxmdVer="11.0">

to 

<AlteryxDocument yxmdVer="10.6"> or whatever version you have? i'd reupload but i'm not sure what version you have.

[Bren312]

Hi Jarrod, thanks again for your help.  I had to leave early on Friday; otherwise I would have responded then.  I can't figure out how to open the workflow you sent in Alteryx (I'm hoping it's just because I don't currently have the 'Workflow Packager' and not because I'm a complete maroon at Alteryx).  Thanks again for trying to help, though!

[jarrod]

no worries. you shouldn't need the packager since it's only the workflow (yxmd).

Try the new attached version. I just changed the file version in the text file.

if this doesn't work, i'll try a few screenshots.

[jarrod · Accepted solution]

here are a couple screenshots from one example in the workflow:

[Bren312]

Man, that is slicker than snot!  I think either of those will work perfectly for me; thanks for all of your time Jarrod!  I appreciate it!

[jarrod]

my pleasure!