What would be the correct Multi-Row formula in this case?

Question

I have a requirement where only the first value when [Bin Type Description] is not null needs to be extracted.

Dataframe:

RecordIDDataBin Type DescriptionQtyPriceTotal569Servicing    570ICS 26L Flick Sanitary Disposal Units 27.00 6.89 201.53ICS 26L Flick Sanitary Disposal Units276.89201.535721.4L Metal Sharps Container 10.00 23.32 252.631.4L Metal Sharps Container1023.32252.63575Clinical Waste 19.00 41.34 850.91Clinical Waste1941.34850.91

I want the result from the multi-row formula to be:

RecordIDDataBin Type DescriptionQtyPriceTotalComments569Servicing     570ICS 26L Flick Sanitary Disposal Units 27.00 6.89 201.53ICS 26L Flick Sanitary Disposal Units276.89201.53Servicing5721.4L Metal Sharps Container 10.00 23.32 252.631.4L Metal Sharps Container1023.32252.63 575Clinical Waste 19.00 41.34 850.91Clinical Waste1941.34850.91 579Blewitt Springs Fine Eggs Pty

Whereas I am using the formula:

IF !ISNULL([Bin Type Description])
THEN [Row-1:Data]
ELSE NULL()
ENDIF

I get:

RecordIDDataBin Type DescriptionQtyPriceTotalComments569Servicing     570ICS 26L Flick Sanitary Disposal Units 27.00 6.89 201.53ICS 26L Flick Sanitary Disposal Units276.89201.53Servicing5721.4L Metal Sharps Container 10.00 23.32 252.631.4L Metal Sharps Container1023.32252.63ICS 26L Flick Sanitary Disposal Units 27.00 6.89 201.53575Clinical Waste 19.00 41.34 850.91Clinical Waste1941.34850.911.4L Metal Sharps Container 10.00 23.32 252.63579Blewitt Springs Fine Eggs Pty

Whereas when I use this formula:

IF !ISNULL([Bin Type Description]) and ISNULL([Row+1:Bin Type Description])
THEN [Row-1:Data]
ELSEIF !ISNULL([Bin Type Description]) and !ISNULL([Row+1:Bin Type Description])
THEN Null()
ELSE NULL()
ENDIF

I am getting

RecordIDDataBin Type DescriptionQtyPriceTotalComments569Servicing     570ICS 26L Flick Sanitary Disposal Units 27.00 6.89 201.53ICS 26L Flick Sanitary Disposal Units276.89201.53 5721.4L Metal Sharps Container 10.00 23.32 252.631.4L Metal Sharps Container1023.32252.63 575Clinical Waste 19.00 41.34 850.91Clinical Waste1941.34850.911.4L Metal Sharps Container 10.00 23.32 252.63579Blewitt Springs Fine Eggs Pty

While I want:

RecordIDDataBin Type DescriptionQtyPriceTotalComments569Servicing     570ICS 26L Flick Sanitary Disposal Units 27.00 6.89 201.53ICS 26L Flick Sanitary Disposal Units276.89201.53Servicing5721.4L Metal Sharps Container 10.00 23.32 252.631.4L Metal Sharps Container1023.32252.63 575Clinical Waste 19.00 41.34 850.91Clinical Waste1941.34850.91 579Blewitt Springs Fine Eggs Pty

How can I get the multi-row formula correct?

Thanks

KarolinaRoza · Answer

Thanks @apathetichell I was not aware .

Karolina

apathetichell · Answer

@KarolinaRozaFYI - ISEMPTY() flags null values as well so you don't need both - just the  !ISEMPTY combo.

KarolinaRoza · Answer

hi @HW1

Firstly I would recommend Data Cleansing tool to remove any whitespaces etc.

then you can apply formula:

IF ISNULL([Row-1:Bin Type Description]) or isempty([Row-1:Bin Type Description])
THEN [Row-1:Data]
ELSE NULL()
ENDIF

but this will provide comment to next row if any of previous [Bin Type Desc] is null/empty.

Do you want to achieve this?

in your example there is record id: 579 with null in [Bin Type Desc] and I an not sure if you want to assign comment to next row as well.

Karolina