Formula in Magic ETL

Manasi_Panov

Hello,

As a newbie in Domo BI, I would highly value your assistance in resolving an issue that I have been unable to solve.

I want to write a formula in Magic ETL that does the same function as the one in Excel from the picture: Compate D2 and D3 and if they are the same AND M3 is blank, take the value from N2. If not, then take the value from M3.

MichelleH

Hi @Manasi_Panov in order to compare values from different rows, you will need to first use LEAD function in a Rank & Window ETL tile ( https://domo-support.domo.com/s/article/360044876094?language=en_US ) to identify the values of Work Type and Order on the row above. Then you can use a CASE Statement in a Forumla tile like this:

case 
  when Order = Next Order and WG Combi is NULL then Work Type
  else WG Combi
end

Manasi_Panov

Hi @MichelleH

Thank you for your input, however, I have some concerns. Firstly, I think I require the LAG function instead of LEAD since I want to compare the Order of the previous row, correct?

The second problem is that the Work Type column doesn't exist. The formula with the logic I shared in Excel is creating the Work Type column, so there is no way to use the LAG function on it. Nonetheless, I appreciate your assistance and looking for your answer.

Manasi_Panov

Hi @MichelleH ,

Actually, I think I was able to do it your way, but I still, have some issues. Here is the result for one Order:

I used the LAG function to create the "Previous Service Order" based on the "Service Order" and then used the LAG function again to generate the "Work Type Temp" from "WG Combi". Then, I used the following formula for the "Work Type" column. However, the last row is still empty and it ought to take the value from "Work Type Temp" - IQOQ. Probably, my formula syntax is messed up. Could you please assist?

Manasi_Panov

Hi @MichelleH ,

I found the problem. The "empty" cells were not exactly empty. There was " " in them. But now I'm facing another problem :)

In cases like this one:

I got: