I have a date column (string) in the following format:
Week 1, 2019
Week 2, 2019
Week 3, 2019
I want to convert it to DD/MM/YYYY so that Tableau would recognize it as a date input. Is it possible to do such manipulation in Knime?
The (DD) part should be the 1st day in the week.
Thanks a lot.
Hi there @amrzahran,
you can use Create Date&Time Range node with configuration like this:
There are two things to configure in Create Date&Time Range node:
- starting point - the date you want to be the 1st day in week
- number of rows - that would be the number of rows in your input data
The latter you can do dynamically (if there is need for it) in a way that you extract number of rows from input table into a flow variable and use it Create Date&Time Range.
This will create a Local Date column type and considering there is a Tableau integration within KNIME maybe this is enough for Tableau and you don’t need DD/MM/YYYY format. Unless it is not check this topic how to change format type.
@ipazin Thanks a lot for your time and your reply.
As far as I understood this node doesn’t take any input columns which would be a problem in my case. I already have the data extracted from a system with a date in the format of (week no, year) in every row. I am trying to change this format into DD/MM/YYYY.
Here I have a workflow in which I have made the week numbers and the corresponding dates as reference with the Create Date&Time Range as @ipazin suggested and joined the table with the main table to add the dates:
week to date.knwf (297.4 KB)
Just since I did not know whether to start each week from sunday or start from the first day of the year, I created references for both cases. You can choose between them. Or now you can create your own reference with a longer time range. To create a reference with a few years of time range, you can use a Chunk loop after the create date and time range node with the number of weeks in a year as the number of rows per chunk.
@armingrudd @ipazin Thanks a lot to both of you
Works perfectly now, I am still learning so I couldn’t get exactly what Ipazin was referring to.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.