Cannot convert V_WString to Date/Time

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.

Reporting

Date Time

Transformation

Accepted answers

BS_THE_ANALYST

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

datetime.yxmd

All comments

BS_THE_ANALYST

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

datetime.yxmd

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

datetime_pluslogic.yxmd

AttilaBalint

Thank you very much!

I'm a bit buffled as I tried most of the options except that apparently. 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:

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.

AttilaBalint

Yes, it worked very nicely, thank a lot.

Is there maybe a way to avoid this manual clicking? There will be 3 more sheets on the file and it would be awesome if this could be done automatically somehow.

AttilaBalint

Hehe, well, I was expecting that.

They are indeed separate flows and the only common thing is that they will go to the same excel file at the end. I was just hoping there is a way to cram all into one flow with one single click to run it but I kinda new this will not work.

Thank you very much for making my very first Alteryx automation fun and rewarding!

I still have 3 sheets to go, so I might come back with a few questions in the coming days.

Have a nice day!

BS_THE_ANALYST

@AttilaBalint you can indeed cram them all in to one workflow, somehow. It can happen, it'll just depend on how you build the workflow. Trust me. 99.9% sure that can be achieved though!

Anyway, congrats on at least getting it working 💪!

But there will be an easier way to do this that involves less manual work, that's the whole point with Alteryx, we're trying to stop the repetitive manual tasks and automate them. Just gotta figure out how to build it 😂😎.

All the best,
BS

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703