How to convert a string to date
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi I have a dataset which has dates in it with different pattern for example below
E Name |
Jun-Dec Client |
22/12 Tesla |
Marry ltd |
Tesla YE2023Dec31 |
Juno 2019 |
For the above I have created another datasheet which is basically tells me the pattern and gives me the month for that record
E Name | Month |
(12) | 12 |
22/12 | 12 |
Sep | 9 |
Mar | 3 |
Dec | 12 |
Jun | 6 |
-Dec | 12 |
Now I am joining both the records using find and replace tool, the first data goes to the find part and the second dataset goes to the replace part but because of this I am getting wrong results. Below is the result example which I am getting
E Name | Month |
Jun-Dec Client | 6 |
22/12 Tesla | 12 |
Marry ltd | 3 |
Tesla YE2023Dec31 | 12 |
Juno 2019 | 6 |
So in the above results you can see the first is wrong because it should have picked the '-Dec' one, So as 3rd is also wrong because it is reading it as Mar and same goes with the last one as well.
Can you please let me know if this is the right approch and if not what I can change to make it correct.
Note: I cant use if else condition because the patter could go up in the large amount
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello @Sshasnk
To be frank, I won't be optimistic there. Automation means you can specify a rule to find the date and from what I read, there is no rules that can really apply. Why is your dataset so diverse ? if it's because it comes from different sources, maybe you can apply rules by sources. if it's because the field you use is a "free text" input, I think it's dead to automate the thing (you can still iterate to find the dates and deal with your current dataset but you will have to have an eye on it when the data will change... I also advise you to see with the data provider to set rules in input).
Best regards,
Simon
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@binuacs As I mentioned above the results are coming wrong using find and replace. Example -Dec should be 12 and Marry should be null. But it is not working but for some reason it works on contain function but I am not able to use contain because it will 100 lines of if else condition. But the result you posted is wrong, sorry 😔
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Sshasnk I thought that was your expected result, can you post the expected result based on your input files?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
E Name | Month
Jun-Dec Client | 12 -> according to second dataset it should be 12
22/12 Tesla | 12 -> This is correct
Marry ltd | NA -> no value matched
Tesla YE2023Dec31 | 12 -> this should be 12 according to second dataset
Juno 2019 | NA -> no value matched
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Sshasnk Let me know the below approach works for you
![](/skins/images/33644471E927638F516C93A75ADDEE18/responsive_peak/images/icon_anonymous_message.png)