I have Data Like Below and I need to write output excluding the columns which has no data, In this case Columns till 31-Mar-2024 (Excluding all the null columns after 31-Mar-2024 columns), and the same flow should write output of all columns when data is available in future executions.
Item | Profit Split % | 31-Mar-24 | 30-Apr-24 | 31-May-24 | 30-Jun-24 |
Chair | 14.31% | 28508873.05 | |||
Pen | 7.03% | 13998997.02 | |||
Table | 11.93% | 23757394.21 | |||
Mouse | 66.74% | 132946378 | |||
Keyboard | -14.09% | -145000000 |
I have tried using data cleansing , but it is not dynamic, when new columns are added it won't pick, Is there a way to achieve this?
Current Required output:
Item | Profit Split % | 31-Mar-24 |
Chair | 14.31% | 28508873.05 |
Pen | 7.03% | 13998997.02 |
Table | 11.93% | 23757394.21 |
Mouse | 66.74% | 132946378 |
Keyboard | -14.09% | -145000000 |
Future require output when data is available:
Item | Profit Split % | 31-Mar-24 | 30-Apr-24 | 31-May-24 | 30-Jun-24 |
Chair | 14.31% | 28508873.05 | 4545 | 645666 | 69355 |
Pen | 7.03% | 13998997.02 | 2545 | 54654 | 6446596 |
Table | 11.93% | 23757394.21 | 15563 | 54684 | 89589589 |
Mouse | 66.74% | 132946378 | 54656 | 516555 | 89899 |
Keyboard | -14.09% | -145000000 | 5656 | 21625 | 454545 |
Hi,
The above solution works, but if all you need is to remove Null columns, the Data Cleansing tool has this built in.
This will work dynamically.
Please let me know if this doesn't help in your case. You may need to set 'blank' (empty) cells to Null() before in some cases, but this should work given the data shown.
Cheers
Is there a n other way rather then using transpose and data cleansing? Using both will extent the workflow so don't want that to happen, let me know if there is an other way like using multi field formula tool
What do you mean by extending the workflow?
@Alteryxexpert are you trying to achieve it by single tool?
while you say not to increase the length of workflow.
Hi @Alteryxexpert, I added a solution above which uses the Data Cleansing tool, performing the task in one tool. Does that work for you?