converting string week to date week format

[Wafarian]

hello,

I would like to convert a string week to date format that will be  recongnize as date format in another system.

for example I have a string week format 202250 and I would like to convert it to date format e.g 2022-50 or 50.2022. 

I have tried using yyyyMM but it is not what I need.

thanks

[ShankerV]

@Wafarian 

Please find the output expected.

[ShankerV]

@Wafarian 

Please find where 50.2022 is also possible.

Left([Field1],4)+"-"+Right([Field1],2)

Right([Field1],2)+"."+Left([Field1],4)

If this helps, please like this post and mark it as a solution. If you have any other questions, please let us know.

Many thanks

Shanker V

[Felipe_Ribeir0]

Hi @Wafarian 

One way of doing both options

[ShankerV]

@Wafarian 

As per the ORACLE date formats, the YYYY-Week of the year is not a valid date formats.

https://docs.oracle.com/cd/E41183_01/DR/Date_Format_Types.html

Hence we can convert 202250 to 2022-50 or 50.2022 as string only.

If this helps, please like this post and mark it as a solution. If you have any other questions, please let us know.

Many thanks

Shanker V

[Wafarian]

Hi,

thanks for your input, the week is still not recognize as date format in SAP analytics cloud as shown and the reason is because the data type is string.

what I meant is that I need the data type for week to be Date format so that it is recognize in SAP analytics cloud.

thanks

[ShankerV]

Hi @Wafarian 

Please find the format which is accepted in SAP also. So we can use the Date column itself. which is the right input.

Many thanks

Shanker V

[Wafarian]

Hi,

Yes, we can convert string to date format in Alteryx and that should work in SAP analytics cloud because the data type is date format.

However, my requirement in SAP analytics cloud is to use variance to compare current week and previous week. And to my understanding, variance will only work if the data type is of date format.

I don't know why we can't have week as date data type in Alteryx as in SAP system.

[ShankerV]

@Wafarian 

Please be informed that, SAP also does not accept the date format as YYYY-Week of the year.

Below is the reference for you circled in red color.

I strongly believe you need to input the datas in SAP and then use the necessary SAP analytics cloud is to use variance to compare current week and previous week using tools like 

Many thanks

Shanker V