Dynamic Columns from Cross Tab - Join doesn't keep columns together

[flipperdm]

Hello,

I've looked through the discussions and can't find a solution to this question.  I have table that adds new dates to a column as time passes. Each day I run a workflow that cross tabs this date column, converting the results into their own header.  The issue is that every few days, when new dates appear, they don't show up immediately next to the other columns that appeared in the previous workflow run.  Example:  I have 2 tables.  The first table is dynamic (new dates) while the second table is static.  When I set this up the first time and run the workflow, the dates from Nov 5th to Dec 12th appear together.  Then, 2 days later, I run the workflow and December 15th shows up in the date column.  That new date will then appear somewhere else in the results.

How do I ensure that my cross tabbed dates always stay together when joined with other tables further down the flow...especially when new dates appear?

Store	Ship Date	Cases
11	12/5/2018	5
11	12/6/2018	10
11	12/7/2018	30
14	11/5/2018	5
14	12/6/2018	105
14	12/10/2018	88
15	12/1/2018	107

Store	Region	Country
11	1	CA
14	2	CA
15	3	CA

Store	12/5/2018	12/6/2018	12/7/2018	12/10/2018	Region	Country	12/15/2018
11	1	1	1		1	CA
14	1	1			2	CA	1
15				2	3	CA	1

[ChrisTX]

See if this post helps:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Sort-Fields-while-Preserving-Data-Type...

[danilang · Accepted solution]

Hi @flipperdm 

I'm sure that @ChrisTX's solution will work(with some tweeking) but there are a couple of options you can use that don't require python tools

1.  Join to your store table after you performed your crosstab.  This way the store data is always added after the data fields.  My workflow reads the data from an excel sheet. 

Here's the output from the first run

Here's the output after I added anther record to the excel. Notice how the date fields are always before the store data.

2. Move the  *unknown fields in the Join configuration to before the store data fields.  This way any new fields show up first

Dan

[flipperdm]

This worked.  The final workflow had 4 more joins which I had to ensure that "dynamic" appeared right after the date fields.  I also had to make sure that the "left's" were always before the "right's"

Thanks!

[flipperdm]

Did need to go to this level but thanks for providing the option :)