topic Re: Creating a new column in the script in App Development

Creating a new column in the script

jan2 — Sat, 16 Nov 2024 17:34:11 GMT

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.

Re: Creating a new column in the script

micheledenardi — Wed, 27 Jan 2021 10:54:06 GMT

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

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

Re: Creating a new column in the script

jan2 — Wed, 27 Jan 2021 12:57:58 GMT

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?

Re: Creating a new column in the script

micheledenardi — Wed, 27 Jan 2021 12:07:35 GMT

Yes, it works in that case as well:

Re: Creating a new column in the script

jan2 — Wed, 27 Jan 2021 12:18:12 GMT

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?

Re: Creating a new column in the script

micheledenardi — Wed, 27 Jan 2021 12:26:49 GMT

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

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

Re: Creating a new column in the script

micheledenardi — Wed, 27 Jan 2021 12:46:28 GMT

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

WeekName(YourDateTimeField) as YearWeek

Re: Creating a new column in the script

jan2 — Wed, 27 Jan 2021 12:51:29 GMT

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

Re: Creating a new column in the script

micheledenardi — Wed, 27 Jan 2021 13:12:16 GMT

please post some example data

Re: Creating a new column in the script

jan2 — Thu, 28 Jan 2021 09:50:04 GMT

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[]

Re: Creating a new column in the script

micheledenardi — Thu, 28 Jan 2021 09:57:38 GMT

[Table Name]: Load Data1, WeekName(Data1) as [YearWeek Data1], Data2, WeekName(Data2) as [YearWeek Data2], Data3, WeekName(Data3) as [YearWeek Data3] From[Data Source] CONCATENATE (Table Name) Load Data 1, WeekName([Data 1]) as [YearWeek Data 1], Data 2, WeekName([Data 2]) as [YearWeek Data 2], Data 3, WeekName([Data 3]) as [YearWeek Data 3] From[Data Source] Store[]