Filtering Certain Account and Date Formatting

[SH_94]

Hi Community,

I would like to seek for guidance on how to do the filtering on the specific account and change the multiple date format as below screenshot.

I had uploaded the excel file on the data related to this result. Appreciate if someone can help me on this.

Many thanks for your help

[Qiu]

@SH_94 
What is your cretiria for the filtering?

[SH_94]

Hi @Qiu ,

I would like to filter specific gl account as below: 

a) If the system are BCD,WUE and ETR, take all the Gl account

b) If the system is ABC, i would like to filter and take this five account as below:

i) 220003

ii)340441

iii)222210

iv)222001

v)122000

After the above filtering is done, i would like to convert all the different date format to the format , ie : 2020-12-24.

Is it possible to do the filtering in the alteryx? I had updated my file below and would like how we need to filter it in the Alteryx.

[Qiu]

@SH_94 
Hope this works for you.

[SH_94]

Hi @Qiu ,

Thanks a lot for the sharing of the workflow. It really helps a lot in designing the workflow.

I was able to successfully run those GL filtering in my workflow. However, i had encountered a bit of problem in running the date format. The alteryx result show the error as below :

After long hour of consideration, i had planned to come out with the format below . Reason being is that some date format are not the right format according to the Alteryx format. Hence, instead of asking Alteryx to read the date format straightaway, i planned to ask Alteryx to detect the format first and run the date format.

For instance, in my original input data file , i had included one column for Format which it would be easier for Alteryx to detect as per screenshot below.

I had included the file below for your reference and would like to ask it is possible if we do it in this way in Alteryx? As i keep thinking on how to solve the date format especially if the date format is not the right format and there are multiple of date in the list. Hence, i had come out with the tagging solution by creating the type of format for each date in order for Alteryx to run it.

It would be great if we can do it via tagging solution.Lastly, can we still use REGEX date formula in this case?

Let me know if this is not so clear.

[Qiu]

@SH_94 
Thank you for the details.

Format E and be same as Format A and Format B, C, F can be 2 format only.
I have change the data as above, please review.

[SH_94]

Hi @Qiu ,

Thanks a lot for the prompt response.

Would like to further understand on the format B,C and F. May i know what it mean by 2 format only in this case? Is it mean that Alteryx can only read Format B and C? If this is the scenario,

do we able to it brutally for format F?

[Qiu]

@SH_94 

These 3 format can basically covered by these two formats

mm/dd/yyyy or dd/mm/yyyy, right?

So I was saying only two formats are necessary.

For 41235, this is tricky, it is numeric value? so can you provide a fomula on how to parse it to Date?

I was assuming it use the ToDate Function,  which represents the number of days since 01-01-1900.

Format B 10/23/2012
Format C 11/12/2018
Format F 11/11/2011

---

---

[SH_94]

Hi @Qiu ,

Thanks a lot for the great information.I not sure if the date format had been changed when u open the file (referring to the format B). Basically the format as per my original post will be as follow:

I had just included the new date format for the file below and would like to seek your advice on this format.

[ken_yap_au]

This Alteryx Challenge and the related comments and solutions should be most of the content and structure that you need to parse different dates within the same field. 

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-4-Date-Parsing/td-p/36731