Formula in Magic ETL
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.
Answers

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 ( ) 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
andWG Combi
is NULL thenWork Type
elseWG Combi
end1 
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.
0 
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?
0 
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:
0
Categories
 10.5K All Categories
 7 Connect
 917 Connectors
 250 Workbench
 465 Transform
 1.7K Magic ETL
 69 SQL DataFlows
 477 Datasets
 193 Visualize
 252 Beast Mode
 2.1K Charting
 11 Variables
 17 Automate
 354 APIs & Domo Developer
 89 Apps
 3 Workflows
 20 Predict
 5 Jupyter Workspaces
 15 R & Python Tiles
 246 Distribute
 62 Domo Everywhere
 243 Scheduled Reports
 21 Manage
 42 Governance & Security
 173 Product Ideas
 1.2K Ideas Exchange
 12 Community Forums
 27 Getting Started
 14 Community Member Introductions
 55 Community News
 4.5K Archive