-
switch function not working with 'dayOfWeek'?
Hi all, I tried to apply a condition to find the latest previous weekday date, so for example, if today is Sunday or Monday, then it will Friday, for other days it will just be yesterday. I tried using switch function like this: switch( datePart(now(), 'dayofweek'), 7, trunc(inc(asDate(now(), 'yyyy-MM-dd'), -2,…
-
Looking to replicate a SUM(COUNTIF) formula in Dataiku
I am working on a scorecard in Dataiku and I would like to calculate the percentage of completion in a set number of columns. Basically, I would like to replicate this formula in excel: =SUM(COUNTIF(ColumnX:ColumnXX,"*")/Total Number of Columns) and am having issues. The columns are a mix of strings, integers, and text,…
-
How to correctly do time conversions
I have a column that has been parsed and is in UTC, when I try to format the date to be in eastern / New York time I get a new column that is -5 hours, but isn't the current the current difference -4 hours? I'm sure this has something to do with daylight savings time vs normal time, but I just want to ensure that my…
-
How to return current date and not time
data set full of jobs and each job has a start date, so I want to create a formula that says filter out my data where my start date hasn't happened yet / less than the current date. The issue I'm having is when I create my now() function it gives me the current date and time, I only need the current date in a parsed date…
-
How to ad leading 0s
I have a column called minutes and that can range from 0 - 60, what I'm trying to do is add a leading 0 to the single digit values, ex 9 = 09, so that no matter what my value is always 2 digits long, is that possible? and if so, how would I accomplish that? Operating system used: windows
-
Week number from day number
We consider that we have a table that contains a column "Day". In this table we have values like "21", "10", "16", .... anyvalue that could correspond to a regular day number from a date. Starting from this, is there ay formula that could return me the week number of the year corresponding to this day number (for this…
-
How to filter dates less than current day
I have a dataset that has a list of dates and days of the week, is there a way I can filter my dataset for dates that are less than or equal to the current date? I tried the formula date = now() but I don't think that's working, it just filters all my data out. Operating system used: windows
-
How to create a sequence number
I have a table with four 3 columns, date, color and time. Im trying to create some kind of sequence field that takes into consideration the first time the color was seen on a specific day. Ex. green was seen on 4/24 at 15:00 and again on 4/24 at 15:30 and again at 15:45, because 3 is the earliest I want that sequence…
-
how to use multiple if functions to create columns
I have 2 columns "label" and "value" and I'm trying to create a third column called category by using the contains function on the label column. I can use if( contains(label,"food"),"food","not food") which basically says if the label contains food, then food else not food. but how can I use multiple contains within an if…
-
Coalesce function doesn't work properly in prepare recipe
Hi everyone! I have a question regarding the coalesce recipe in dataiku. I wanted to use the coalesce funtion in dataiku Formula and the preview that I have in the prepare recipe shows that the function works and it shows the value that I want. But after executing the recipe, the resulting column shows a different output…