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

Formating Vertical Set of Dates to become Columns and populating applicable bill date

JohnDella
7 - Meteor

Hello,

 

I am new to Alteryx and I am having a tough time finding a solution to formatting my "Start Range" & "End Range" data to become column headers.

Then I need to populate an applicable Bill Amount that falls in between the range, per another columns Bill date. Set of Data to be horizontal.PNGBelow is the Original Data:

 

 

 

 

and I need to turn it into this:

End Result.PNG

I also am up for another suggestion of formatting, as long as the various Rental Bill date and It's Applicable Amount can can fall underneath the  beginning bill week cycle.

any suggestions?

 

Thank you!

4 REPLIES 4
PhilipMannering
16 - Nebula
16 - Nebula

Alteryx's strength isn't really formatting. And you'll never get it perfect because Alteryx will complain if you have two or more column names the same.

But might have got some of the way there. See attached.

 

Format output - Capture.PNG

danilang
19 - Altair
19 - Altair

Hi @JohnDella 

 

@PhilipMannering has a great solution for your sample data set, but using a RecordID tool to generate the Cross-tab column headers doesn't allow you to have more than 1 WAV # without duplicating the date columns for each wave.  The attached WF changes the RecordID to a Multi-row to handle this situation.

 

Since you also mentioned that you were open to other formatting options, I added a second wf that generates week based column headers.  

 

wf.png

 

This has consistent column headers even if the bill date varies between WAV #s as show below

 

results.png

 

Dan

JohnDella
7 - Meteor

Thank you. This was very helpful

JohnDella
7 - Meteor

Thank you. This was perfect, as I do have more WAVs, and they duplicate too. This workflow solved my problems of formatting and bringing in the applicable bill amount.

 

Labels