user026529

But the formula in beast mode validates just fine

It doesnt compute when I do the same in Beast mode

ahh.. yes so when I correct the syntax it works. One more Question -Is it possible to do this in beast mode? rather than ETL? Because I see that beast mode has a function for Sum and ETL formula box doesnt support Sum So I want to do something like this POWER((Sum(`DaysPast`)) / Sum(`Lag`)),.2)-1 Basically sum these 2…

No the revenue columns dont have null values see the screenshot.. its not validating itself

@MarkSnodgrass Sorry this approach is not working for me as I am not able to see the preview. In the filter workflow (previous to my case formula) doesnt show me any preview of any rows (It has a green check when I run the preview) and yet it gives the rows perfectly in the output table when I run the dataflow I tried…

@MarkSnodgrass Yes as you can see my previous post all my operations have a green check against them when I run the preview

@MarkSnodgrass Its funny that it says no rows match your criteria here, yet I do get my 1st condition that creates 'G' in the final table

@MarkSnodgrass Yes I did that. I ran preview and then tried to see my data in the case formula operation

@MarkSnodgrass My case statement doesnt show me any error on GRY, a column that i created with this condition (Screenshot below) however I dont have preview available in magic ETL

@MarkSnodgrass Ok an update- It doesnt work as planned. In my final update table I have my groupby column that is left join on my main table, however the column has only 'G' values (Wherever there are values) and no R1 or R2s etc.. it looks like only 1st statement in the case statement below is executed and not the rest.…

@MarkSnodgrass Thanks for this. In group by configuration, I have configured as below with left join on my original table. The only issue is that now I have ReqID as a common column in my original table and my group by table. If I choose to rename it on the group by table, will it impact group by step?

@MarkSnodgrass can you provide me a screenshot of group by function configuration in this case?

The first Add Formula just has your case statement in it and I created a column called Group. (Note: in your case statement, you are using <> ''. You'll know your data best, but I had to change it to IS NOT NULL because that my blank rows are null. You may want to test for that.) I agree with you 'is not null' is a better…

Consider that request status is open for all of these

Ok so my case condition are logically arranged, meaning if 1st case condition is executed then all other case conditions are invalid, and that req id where 1st condition is met needs not to go through other case conditions, and hence that req Id doesnt need to be checked for next conditions. So In this case assuming if I…

Ok but as I mentioned earlier, that I have duplication in Req IDs. So lets say a Req ID satisfy 1st case condition, then I want all the rows where that ReqID exist to be written with "G" in that new column I am creating. Even if another row (down the lane) that has the same req ID doesn't meet my case 1 condition. Will…

Ok but I am not flagging the row itself. I am just adding another column in the transformation that will basically do the flagging (Based on case values it evaluates in the Case statement). So its just adding a column

Then what was the meaning of the error as in the screenshot?

Thanks. I realized that my syntax error was because I was using IN with WHEN. The moment I removed IN the error went away, but the way DOMO described the error was kind of misleading and not helpful. It should have created an error on IN statement.

So basically its because "<" or ">" operator are not supported for IN statement? Also I noticed that you had a parenthesis before CASE and after the END. does case statement needs to be in parenthesis?

So Just for understanding, why can I not use IN statement in this syntax? because I saw a similar application of IN statement in documentation here https://knowledge.domo.com/Visualize/Adding_Cards_to_Domo/KPI_Cards/Transforming_Data_Using_Beast_Mode/02Beast_Mode_Functions_Reference_Guide

But then what happens for values between 6 to 8, because in 2nd statement anything under 8 will flag as passive, but I want them as detractors and only between 6-8 as passive

I tried again with changed syntax, but again same error.