(Week Number, Year) to DD/MM/YYYY

[amrzahran]

Hello Everyone,

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.

[ipazin]

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.

Br,
Ivan

[amrzahran]

@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.

[armingrudd]

Dear @amrzahran,

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.

[amrzahran]

@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.