Bring your best ideas to the AI Use Case Contest! Enter to win 40 hours of expert engineering support and bring your vision to life using the powerful combination of Alteryx + AI. Learn more now, or go straight to the submission form.
Start Free Trial

Alteryx Designer Desktop Discussions

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

Dynamic Account number and value question (in Crosstab)

Bren312
8 - Asteroid

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

 

 

8 REPLIES 8
jarrod
ACE Emeritus
ACE Emeritus

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
8 - Asteroid

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
ACE Emeritus
ACE Emeritus

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
8 - Asteroid

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
ACE Emeritus
ACE Emeritus

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
ACE Emeritus
ACE Emeritus

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

Field Header 1.pngField Header 2.png

Bren312
8 - Asteroid

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
ACE Emeritus
ACE Emeritus

my pleasure!

Labels
Top Solution Authors