Alteryx Designer Desktop Discussions
Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
Hi All,
How to categorize the data based on word, I want to make my data as below format could you. Please suggest how to format data as shown below.
| Field | Data | ExpectedOutput | Field | Data | TempId | ||||||||||
| Announcement Sequence Code: AB22 | Announcement Sequence Code: AB22 | 0 | |||||||||||||
| Announcement Type | Test | Announcement Type | Test | 0 | Announcement Type | Ex-Date | Entry Date | Payment Date | Record Date | Subject to Approval | Declaration Date | Close of Business/Dividend Declaration Code | |||
| Ex-Date | Test | Ex-Date | Test | 0 | Announcement Sequence Code: AB22 | Test | Test | Test | Test | Test | Test | ||||
| Entry Date | Test | Entry Date | Test | 0 | Revision to Announcement: AB22 | Test1 | Test1 | Test1 | Test1 | Test1 | Test1 | Test1 | Test1 | ||
| Payment Date | Test | Payment Date | Test | 0 | Announcement Sequence Code: AC22 | Test2 | Test2 | Test2 | Test2 | Test2 | Test2 | Test2 | |||
| Record Date | Test | Record Date | Test | 0 | Announcement Sequence Code: AD22 | Test3 | Test3 | Test3 | Test3 | Test3 | Test3 | Test3 | Test3 | ||
| Subject to Approval | Test | Subject to Approval | Test | 0 | |||||||||||
| Revision to Announcement: AB22 | Revision to Announcement: AB22 | 1 | |||||||||||||
| Announcement Type | Test | Announcement Type | Test1 | 1 | |||||||||||
| Ex-Date | Test | Ex-Date | Test1 | 1 | |||||||||||
| Entry Date | Test | Entry Date | Test1 | 1 | |||||||||||
| Payment Date | Test | Payment Date | Test1 | 1 | |||||||||||
| Record Date | Test | Record Date | Test1 | 1 | |||||||||||
| Declaration Date | Test | Declaration Date | Test1 | 1 | |||||||||||
| Close of Business/Dividend Declaration Code | Test | Close of Business/Dividend Declaration Code | Test1 | 1 | |||||||||||
| Subject to Approval | Test | Subject to Approval | Test1 | 1 | |||||||||||
| Announcement Sequence Code: AC22 | Announcement Sequence Code: AC22 | 2 | |||||||||||||
| Announcement Type | Test | Announcement Type | Test2 | 2 | |||||||||||
| Ex-Date | Test | Ex-Date | Test2 | 2 | |||||||||||
| Entry Date | Test | Entry Date | Test2 | 2 | |||||||||||
| Payment Date | Test | Payment Date | Test2 | 2 | |||||||||||
| Record Date | Test | Record Date | Test2 | 2 | |||||||||||
| Declaration Date | Test | Declaration Date | Test2 | 2 | |||||||||||
| Subject to Approval | Test | Subject to Approval | Test2 | 2 | |||||||||||
| Announcement Sequence Code: AD22 | Announcement Sequence Code: AD22 | 3 | |||||||||||||
| Announcement Type | Test | Announcement Type | Test3 | 3 | |||||||||||
| Ex-Date | Test | Ex-Date | Test3 | 3 | |||||||||||
| Entry Date | Test | Entry Date | Test3 | 3 | |||||||||||
| Payment Date | Test | Payment Date | Test3 | 3 | |||||||||||
| Record Date | Test | Record Date | Test3 | 3 | |||||||||||
| Declaration Date | Test | Declaration Date | Test3 | 3 | |||||||||||
| Close of Business/Dividend Declaration Code | Test | Close of Business/Dividend Declaration Code | Test3 | 3 | |||||||||||
| Subject to Approval | Test | Subject to Approval | Test3 | 3 |
Thanks & Regards,
Niranjan
Solved! Go to Solution.
Happy new year!!!!
Please find the below expected solution.
Note: The workflow used to achieve the solution is attached which can be downloaded to see how the solution works.
If you believe your problem has been resolved. Please mark helpful answers as a solution so that future users with the same problem can find them more easily!!!!
Many thanks
Shanker V
Hi @NiranjanK1
I hope you are expecting the below output.
Made multiple amendments in the workflow as the new dataset shared was completely new from the sample dataset shared.
I made sure the sequence is also not changed.
Simplified workflow:
Input used:
Note: The workflow used to achieve the solution is attached which can be downloaded to see how the solution works.
If you believe your problem has been resolved. Please mark helpful answers as a solution so that future users with the same problem can find them more easily!!!!
Many thanks
Shanker V
Hi @NiranjanK1
I found many data irrelevant, hence used Find and Replace tool to get the output.
Can use formula tool also but my condition was very big, but still we can use Formula tool based on your.
Let me know if it works for you. If else please reach out to community, with your questions.
Hi @NiranjanK1
Sure, happy to help/explain.
1. If I need to use Formula tool, then I would have used the below formula
IF contains([Field], "Revision to Announcement:" or contains([Field],"Ex-Date" and typed all the possible which will occur
then "T"
else null()
endif
This will create a new column with T
Then I am using the filter tool to eliminate all the [Null] and getting the desired input to use in the workflow.
2. As I felt why I need to type huge formula, I applied the Find and Replace tool.
Where I used the fields which need to be considered for processing. In future if you need to any new field for processing, you can just add or remove.
Then those are marked as T and the same Filter is applied.
Hope this helps!!! If you need more information, feel free to ask in the community. Happy to help!!!
Many thanks
Shanker V
