Keeping Same Number of Columns Created via Cross Tab Each Run

I have a workflow where I'm using a formula tile to assign a number to a record (new field) based on whether it falls before or after a date as follows:

IF [Sales GL Date] > '2022-05-31' THEN 2 ELSE 1 ENDIF

In the next step, I utilize a Cross Tab tile to pivot each category (1 & 2 from above) into separate columns summing by amount.

Normally, my data will have dates both before and after and I'll get two columns once I use the Cross Tab tile. However, there are times when all records will be before or after, not both. When this happens, the Cross Tab only creates one column and subsequent parts of my workflow will error out and my output changes to one column instead instead of two.

I would like to have two columns every time I run this workflow regardless of if there is data for both or not. Any assistance is greatly appreciated.

Thank you

Output

Tips and Tricks

Date Time

Expression

Accepted answers

Christine_Assaad

Hi @igquarberg

You can use a simple Text input that will have your headers with the 2 columns; 1 and 2.

You can then use the Union tool so you will always have 2 columns regardless of whether your original data has 1 or 2 or both.

Your 'dataset' text input will be replaced with your crosstab process. Hope this helps.

All comments

binu_acs

@igquarberg Can you provide some sample data and expected output?

Christine_Assaad

Hi @igquarberg

You can use a simple Text input that will have your headers with the 2 columns; 1 and 2.

You can then use the Union tool so you will always have 2 columns regardless of whether your original data has 1 or 2 or both.

Your 'dataset' text input will be replaced with your crosstab process. Hope this helps.

igquarberg

@binuacs - Please see spreadsheet attached. I included Samples 1 & 2 to show both scenarios. Output 2 is where I highlighted the column that doesn't show up because all dates fall under "2" based on the If statement where I want all records in May or earlier to be "1" and June or later to be "2".

igquarberg

It took me a while, but I think I got this to work. I still need to add this into my bigger workflow to really test it, but below is what I ended up doing and it shows the output based on my sample data I prepared for @binuacs. At first, I was thinking you meant to do the text insert before the cross tab process, not after. When I did it before the crosstab, it created more columns. Thanks!

igquarberg

@binuacs - Please see my response to Christine's solution. At first, I was confused by it and I went ahead and did a post providing the sample data you asked for. Of course, after I did that I think I got Christine's to work. I appreciate your willingness to look at this. If there is any other insight you have to add about Christine's solution or another way to do it that you think is easier, please share. Thanks!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703