Creating a new column in the script

[jan2]

Hi,

I have a large data set in which a column with a date exists (format: dd.mm.yyyy). Now I need to create an extra column which shows the respective calendar week and year. I want to implement that in the script. 

I hope someone can help me. 

[micheledenardi]

Use Week() and Year() functions to build something like:

Load *,
week(YourDateTimeField)&'/'&Year(YourDateTimeField) as WeekYear
From YourDataSource;

[jan2]

Thanks, is that also possible when a time is included? Format: "yyyy-mm-dd hh-mm-sec" as you see in the attachment?

Or is there another command that I can use?

[micheledenardi]

Yes, it works in that case as well:

[jan2]

It worked, thanks. But that leads me to the next problem. The first three days of 2021 are labeled as calender week 53. So if I combine week() and year () I get 53/2021. Any idea how I could fix that?

[micheledenardi]

You can fix this bug by replace the expression with this:

week(YourDateTimeField)&'/'&Year(WeekStart(YourDateTimeField)) as WeekYear

[micheledenardi · Accepted solution]

Alternatively you can use WeekName() to get week number e year in YYYY/WW format:

WeekName(YourDateTimeField) as YearWeek

[jan2]

If I try this the first days of 2020 are displayed as 2019.

[micheledenardi]

please post some example data

[jan2]

At what place do I have to put it in the script? At the moment it looks like:

[Table Name]:

Load

Data1,

Data2,

Data3

From[Data Source]

CONCATENATE (Table Name)

Load

Data 1,

Data 2,

Data 3

From[Data Source]

Store[]