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
@ShankerV Thanks for your reply in actaul data i have some data gaps also in that case how can i categeraze data. please find the below actual data for your reference. Please suggest
Field | Data |
Revision to Announcement: AA22 | |
Announcement Type | change announcement |
Ex-Date | 15 Mar 2022 |
Entry Date | 12 Aug 2022 |
Payment Date | 31 Mar 2022 |
Record Date | 16 Mar 2022 |
Declaration Date | 10 Feb 2022 |
Due-Bill-On Date | |
Due-Bill-Off Date | |
UIT Preliminary/Final Announcement | Does not apply |
Close of Business/Dividend Declaration Code | Does not apply |
Subject to Approval | Does not apply |
Revision to Announcement: AC22 | |
Announcement Type | change announcement |
Ex-Date | 13 Sep 2022 |
Entry Date | 12 Aug 2022 |
Payment Date | 29 Sep 2022 |
Record Date | 14 Sep 2022 |
Declaration Date | 11 Aug 2022 |
Due-Bill-On Date | |
Due-Bill-Off Date | |
UIT Preliminary/Final Announcement | Does not apply |
Close of Business/Dividend Declaration Code | Does not apply |
Subject to Approval | Does not apply |
Announcement Sequence Code: AD22 | |
Announcement Type | change announcement |
Ex-Date | 29 Nov 2022 |
Entry Date | 10 Nov 2022 |
Payment Date | 30 Dec 2022 |
Record Date | 30 Nov 2022 |
Declaration Date | 10 Nov 2022 |
Due-Bill-On Date | |
Due-Bill-Off Date | |
UIT Preliminary/Final Announcement | Does not apply |
Close of Business/Dividend Declaration Code | Does not apply |
Subject to Approval | Does not apply |
Announcement Sequence Code: AE22 | |
Announcement Type | change announcement |
Ex-Date | 12 Dec 2022 |
Entry Date | 24 Nov 2022 |
Payment Date | 9 Dec 2022 |
Record Date | 2 Dec 2022 |
Declaration Date | 22 Nov 2022 |
Due-Bill-On Date | 2 Dec 2022 |
Due-Bill-Off Date | 13 Dec 2022 |
UIT Preliminary/Final Announcement | Does not apply |
Close of Business/Dividend Declaration Code | Does not apply |
Subject to Approval | Does not apply |
Supplemental Information for | change announcement |
Equivalent Cash Rate | 0 |
Ratio of No. Shares Received for Each Share Held for 1st Issue | 0.25 |
CUSIP of 1st Issue Distributed | |
Additional Distribution Indicator | |
ADR/ORD Basis of Issuance |
Hi @NiranjanK1
Its possible, where you need to eliminate 3 rows which is not needed.
Step 1: Use formula tool to mark True or False as condition
Step 2: Use filter tool to eliminate the rows
You will get your output.
Many thanks
Shanker V
Hi @NiranjanK1
I have now tried with Find and Replace tool as you might have any more columns which will pop up.
You can use Find and Replace tool also.
Let me know if you need the solution to be build for the same.
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.
@ShankerV Could you please help me, im trying to understanding the above logic how you did using find replace and filtertoo. if possible help me how to build with below logic. It is help me to imporove my tool knowledge.
Its possible, where you need to eliminate 3 rows which is not needed.
Step 1: Use formula tool to mark True or False as condition
Step 2: Use filter tool to eliminate the rows
Regards,
Niranjan
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
@ShankerV I want to move all the data to the flow, i need all the data including blanks to the output, Please suggest me the best way to get the output with full data.