New months from cross tab joined into table

Hello,

New to this community and just starting on Designer.

I have a workflow that takes sales data, transforms the invoice date into a MMM-YYYY format and pivots it into a crosstab by customer. End result is sales by customer with MMM-YYYY as the columns. These sales columns are appended to another table that contains customer attributes like name, address, customer segment, etc. using the Find / Replace tool. Result is a table by customer with sales by month in the columns. Works great.

Issue is calendar has flipped to a new month and the new month does not automatically append to the final table. I see it in the cross tab, but in Find / Replace tool I need to select it manually for it to join final table. I would prefer that happen automatically; I do not want to have to remember to go into flow at beginning of each month and manually select the new month to get my final result.

Any thoughts on how to get the new month to roll into the final table automatically? Thanks in advance!

Join

Accepted answers

jrlindem

@rssdbr

Okay, I would....

Grab just the [Customer] field from both sources and union them, remove duplicates using Summarize Group By or Unique Tool.

Then join / find-replace your attributes from the original tables. That way you always have something to append to as the "master customer id list" and then you can do your downstream appends, and pivots, etc.

Like this:

Also attached. Note I realize I didn't convert your dates to the format you wanted, but trust you can do that part!

Hope this helps, -Jay

jrlindem_example.yxmd

All comments

Qiu

@rssdbr
Can you share some sample data or mocked data as work flow input and also desired output so we can take a closer look?

jrlindem

@rssdbr Is this an issue that there is no data that reflects the new month? So, then there is nothing that can be appended (or Find-Replace'd) to?

If so, then you could consider using Generate Rows Tool to create date values within certain constraints so that there is always a date to tie the data together. You would use that as the bonafide left side of your joins, then join the invoice data (and change date format) and then join the attributes. I would also recommend leaving the date format alone until after all your joins and transformations so you don't have to manage that in multiple streams (unless you have to do it to match formats for the joins).

See if that gets you closer. If you need help with that tool, let us know. Otherwise, as @Qiu stated, supplying a dataset (fake data) along with what you expect the output to look like is very useful for us to be able to mockup how you would accomplish that.

Cheers, -Jay

rssdbr

Appreciate the quick responses. Including quick illustration of process. Taking sales data and pivoting it in cross tab to get totals by customer by month. Then adding in master data for accounts (in this example city and state). I am using Find / Replace tool to append the master data columns to the sales cross tab to get my final desired table.

Problem is when the calendar rolls over to the new month. Cross tab adds the new month (in this example it would be Feb 2026), but the Find / Replace tool does not pick up on the fact a new month column has been added. See image below.

Is there a way for Designer to automatically check the box for a new month (2026_02), or am I relegated to manually updating the configuration each month when the calendar rolls over? Or is there some other workflow that would make this update automatically for each new month? Thanks in advance for any insights.

OllieClarke

Hi @rssdbr

Just do the Find and replace before the cross-tab. Then you'll have the customer information attached to all the months' data

jrlindem

@rssdbr Why are you joining the customer data again when you've already appended upstream? Seems redundant (and causing your issue)

Automatically selecting the new months should be, well, "automatically" handled by Crosstab Tool.

Mind attaching your workflow and dummy data and showing in Excel what your desired output would look like?

-Jay

rssdbr

Final output needs to be a database of customers with sales plus targeted customers without sales. Sales data contains only those customers with sales. Customer master list contains all targeted customers, some who do not have sales. The join is being used to append targeted customers who did not have a sale to the crosstab of customers who did have a sale. (Aside: my first attempt was to join crosstab results to the customer master list, but new months of sales data did not automatically get appended to the end of the output).

In the example below only customers A and B have sales. Customer C is a targeted customer who we want to track in the final database (may seem weird, but we have our reasons). Ideally output would look like:

And workflow example I am testing is:

jrlindem

@rssdbr

Okay, I would....

Grab just the [Customer] field from both sources and union them, remove duplicates using Summarize Group By or Unique Tool.

Like this:

Also attached. Note I realize I didn't convert your dates to the format you wanted, but trust you can do that part!

Hope this helps, -Jay

jrlindem_example.yxmd

jrlindem

@rssdbr Did my latest response get you closer? If so, please close out the post by marking the solutions, else, let me know and we can dig further. -Jay

AncientPandaman

@rssdbr
based on your options, find my answer in blue

1. to come up with a way to automatically select the new months as they come in (which is the issue I had initially using a different flow) or

It does automatic select the new field, it will auto add before the "*Unknown" field, unless you unchecked it.
you can try below and see to the way it interact with new field
a. you add and move the [Total] field below the "*Unknown" field in select tool

b. uncheck the "*Unknown" and add new month.

2. prevent the "right" columns from appearing when joining sales data with customer records that did not have any sales.

you can adjust in the join tool setting. just remove these columns in second half of the join tool. it act as a J anchor's select tool.

rssdbr

Thanks Jay! Tested out with my data and gets the results I am looking for. Sincerely appreciate the help!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703