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.3K Product Ideas
 1.3K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 281 Workbench
 2 Cloud Amplifier
 4 Federated
 2.8K Transform
 81 SQL DataFlows
 541 Datasets
 2.2K Magic ETL
 3.1K Visualize
 2.3K Charting
 509 Beast Mode
 25 Variables
 553 Automate
 129 Apps
 408 APIs & Domo Developer
 16 Workflows
 27 Predict
 11 Jupyter Workspaces
 16 R & Python Tiles
 342 Distribute
 84 Domo Everywhere
 257 Scheduled Reports
 1 Software Integrations
 79 Manage
 78 Governance & Security
 1 Product Release Questions
 Community Forums
 41 Getting Started
 27 Community Member Introductions
 72 Community Announcements
 4.8K Archive