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
 All Categories
 1.6K Product Ideas
 1.6K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 288 Workbench
 4 Cloud Amplifier
 8 Federated
 2.8K Transform
 94 SQL DataFlows
 594 Datasets
 2.1K Magic ETL
 3.6K Visualize
 2.4K Charting
 670 Beast Mode
 42 App Studio
 34 Variables
 645 Automate
 165 Apps
 434 APIs & Domo Developer
 42 Workflows
 4 DomoAI
 31 Predict
 12 Jupyter Workspaces
 19 R & Python Tiles
 378 Distribute
 107 Domo Everywhere
 266 Scheduled Reports
 5 Software Integrations
 109 Manage
 106 Governance & Security
 8 Domo University
 23 Product Releases
 Community Forums
 39 Getting Started
 29 Community Member Introductions
 95 Community Announcements
 4.8K Archive