multi-row formula, Row - 1 , need to make that dynamic somehow

before multi row tool:

FileName	Name	Value
Book (3)	Amt	2
	Amount
	Process

after:

FileName	Name	Value
Book (3)	Amt	2
File Name : does not contain field :	Amount
File Name : does not contain field :	Process

multi row formula:

if isnull([FileName]) then "File Name : " + [FileName] + " does not contain field : " else [FileName] endif

I know I could do this:

if isnull([FileName]) then "File Name : " + [Row-1:FileName] + " does not contain field : " else [FileName] endif

however it will not work. I need to be able to do -2 but in a dynamic fashion. As There could be many rows before row 1 here..

if I did -1 it will look like :

FileName	Name	Value
Book (3)	Amt	2
File Name : Book (3) does not contain field :	Amount
File Name : File Name : Book (3) does not contain field : does not contain field :	Process

any help would be appreciated.

Custom Tools

Accepted answers

binu_acs

@wonka1234 one way of doing this

Screenshot 2023-06-29 155730.png

MissingFileNAme_290623.yxmd

All comments

ed_hayter

I've added another conditon to your multi-row, assuming the names are in a consistent order,

if isnull([FileName]) AND [Name] = "Amount"
then "File Name : " + [Row-1:FileName] + " does not contain field : " elseif
isnull([FileName]) AND [Name] = "Process"
then "File Name : " + [Row-2:FileName] + " does not contain field : " else
[FileName] endif

I've attached the workbook as well.

If your Name column is not in a consistent order you can make a record ID for every 3 rows (https://www.thedataschool.co.uk/henry-mak/modulo-increment-number/) and then assign the names an order rank (amt = 1, Amount = 2, Process = 3) and sort by the recordID, then the measure rank to ensure the correct order

Dynamic Multi-Row.yxmd

binu_acs

@wonka1234 one way of doing this

Screenshot 2023-06-29 155730.png

MissingFileNAme_290623.yxmd

wonka1234

@ed_hayter thanks but my "Name" column will never be the same.. I cant really hardcode this also I dont know how many differnet names there will be..

SPetrie

Another possible method is using regex in the multi-row to test for and replace the name as appropriate.

Im sure my regex could be improved upon, but it shows how the method could work.

if isnull([FileName]) and contains([Row-1:FileName],":") then REGEX_Replace([Row-1:FileName],"
.+:(.+) does.+","File Name : $1 does not contain field :") elseif isnull([FileName]) and !contains([Row-1:FileName],":") then "File Name : "+[Row-1:FileName]+" does not contain field :" else [FileName] endif

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703