I'm trying to create new combined date variable from separated Year, Month, Date and Hour variables as "YYYY/MM/DD HH:MI:SS" format.
However, I can't create it using processor step like below screenshot.
Could you please someone help me how to create new datetime variable as "YYYY/MM/DD HH:MI:SS" format?
You can put the "00:00" in a string format, the formula would look like this concat(year,"/",month,"/",day," ",hour,":00:00").
Once you have that new column, click on the column > Parse date and this will create a new column of Date type.
Thank you for your prompt answer.
I tried to implement guided formula but unfortunately it did not works not fine.
Because month, date and hours variables values does not double digit format. (e.g. 01 -> 1)
So I applied change each column's value digit format if under 9, also added Dataiku default date and time format refer to below link.
As the result, below formula worked fine.
Result -> 2018-11-30T12:00:00.000Z , 2018-01-30T01:00:00.000Z ...
However, this formula little bit messy so please let me know more smarter way if possible.
Hi @aad34210 ,
The month, date and hour variables should not need to be double digit format. As long as the date is a valid one, DSS should be able to parse it to a Date format. Additionally, you can specify the specific date format in the parse step. By default, it's always yyyy/MM/dd HH:mm:ss.
Can you please try parsing the date without formatting the variables to double digit, i.e. copy my formula above and then try parsing the date?
Thank you for your prompt reply.
I tried to implement your guide and it seems looks fine. (No red colors in created column)
But sort order function in this column can't works correctly.
I took screenshot which already sorted using guided formula.
[Explanation of columns in attached screenshot]
- year / month / day / hour : Source columns
- cont_date_check : Created from guided formula
- cont_date: Created from my formula (Yesterday I replied)
You can see cont_date_check column's sort order didn't work correctly.
For example, the value 2017/12/30 6:00:00 should sorted at 2nd row in 2017/12/30, but this value sorted last row in 2017/12/30 ...
Could you please tell me how to modify for correct it ?
If you have any questions please let me know.