Cannot convert V_WString to Date/Time

[AttilaBalint]

Hello,

I'm quite new to Alteryx and could not figure this one out yet despite checking the forum.

So, I have this data which looks like this in the csv file:

This column is deemed V_WString in Alteryx and if I change it to Date with the select tool, it will give the message that it is not a valid date.
So I tried to convert it using the DateTime tool but whatever I write unde custom, I get the below warning:

I would need to be able to set the column to date format as I need to filter it for the current month but since I can't, the filtering formula does not work.

So the question is, do any of you know a way to change this data to date or a way to filter the String so it will give me the current month only. In excel I could work this out by using a formula which includes a current month function converted to text (I could get the JAN, FEB, MAR, etc part) and use that as a "contain" filter option but I have no idea how to do it in Alteryx

Thanks in advance for you help.

[BS_THE_ANALYST · Accepted solution]

@AttilaBalint
Here's one way using the DateTime tool:

[BS_THE_ANALYST]

@AttilaBalint This might also be useful for you (further steps).

I've given two solution routes, (method 1) I think you might take this step but it involves hard coding. Method 2, is more dynamic, using the DateTimeToday() function and extracting the year and month from it. Then extracting the Year and Month from the hire_dates. Then filtering for the ones that match:

Have a look through them! Any questions, let me know. 
BS

[AttilaBalint]

Thank you very much!

I'm a bit buffled as I tried most of the options except that apparently. :D I think the preview version confused me as I can see date in 01-APR-2023 format instead of the 01-Apr-23 one. In any case, thank you very much I was stuck there for a while now.

[BS_THE_ANALYST]

@AttilaBalint hey, no problem. We've all been there 😁

[AttilaBalint]

Thank you :)

This is how I did the filtering but I wonder if there is an easier way to get the current month. I assume there may not be. :)

Also, what I am doing now is that I have two csv files that I configured and now would like to add them to a single excel file on two separate sheets. How I did it first is by adding an output tool for each of them but it of course runs to an error saying the file is being opened by another program. I guess I have to somehow connect the two strings to one single output but could not just figure out how to do it.

[BS_THE_ANALYST]

@AttilaBalint 

First thing:
Okay, you can definitely output your data into separate sheets in one excel workbook! So that's good news, that can all be done in Alteryx.
Second thing:
1. You're getting that error because you probably have one of those excel sheets open on your computer? 
2. Actually, you are trying to write to the same Excel file. So what's happening the top (or the bottom datastream) creates and starts writing to that excel workbook. Meanwhile, the other one is also trying to write to that excel sheet but it's kicking up a fuss because the other one is currently working on it! 

There's plenty of solutions to solve your problems though, that's the good news.

[BS_THE_ANALYST]

@AttilaBalint here's my first suggestion. We are utilise the Tool Containers like this:

Highlight over each of the Output Data tools and put them in a tool container like this:

next:

Then once they are both in their own containers like this press this button I'm pointing to with the arrow to 'disable it':

Then you can run the workflow, it should be able to write to the excel file. If there are no errors, great! You then need to switch the process round by disabiling the other container and enabling the first one you disabled:

Then run the workflow again. This will then allow you to write to the second tab, ensuring the file isn't open from the datastream above. 

I've definitely made that sound way more confusing than it actually is. Hopefully that helps @AttilaBalint 

[AttilaBalint]

That's good news indeed! :)

I think Alteryx itself is using the excel as you said it in point 2 but it is funny because it can change which one it takes first, the first row or the second. I guess it depends which one I modified last.

What I am doing is basically getting a csv report with new hire date and filter it down for current month and then I add the data to a new excel on a new sheet. Then I get another csv report, a future dated hire report, and do the same filtering and then I take the previous excel and would add a 2nd sheet to it but since the first output already worked on the excel, it gives this error message.

[BS_THE_ANALYST]

@AttilaBalint there's a completely different solution here

These are essentially two different workflows, they don't relate to one another as there are no tools joining them together. How about separting them into their own tabs (their own workflows). Run one workflow, the run the other workflow? This way they don't have to be on the same page, and don't need the containers approach!

I'm basically saying put them on their own pages, one per canvas:

Run one, let it be successful, then run the other. Give that a try.