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

[HW1]

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

Dataframe:

RecordID	Data	Bin Type Description	Qty	Price	Total
569	Servicing
570	ICS 26L Flick Sanitary Disposal Units 27.00 6.89 201.53	ICS 26L Flick Sanitary Disposal Units	27	6.89	201.53
572	1.4L Metal Sharps Container 10.00 23.32 252.63	1.4L Metal Sharps Container	10	23.32	252.63
575	Clinical Waste 19.00 41.34 850.91	Clinical Waste	19	41.34	850.91

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

RecordID	Data	Bin Type Description	Qty	Price	Total	Comments
569	Servicing
570	ICS 26L Flick Sanitary Disposal Units 27.00 6.89 201.53	ICS 26L Flick Sanitary Disposal Units	27	6.89	201.53	Servicing
572	1.4L Metal Sharps Container 10.00 23.32 252.63	1.4L Metal Sharps Container	10	23.32	252.63
575	Clinical Waste 19.00 41.34 850.91	Clinical Waste	19	41.34	850.91
579	Blewitt Springs Fine Eggs Pty

Whereas I am using the formula:

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

I get:

RecordID	Data	Bin Type Description	Qty	Price	Total	Comments
569	Servicing
570	ICS 26L Flick Sanitary Disposal Units 27.00 6.89 201.53	ICS 26L Flick Sanitary Disposal Units	27	6.89	201.53	Servicing
572	1.4L Metal Sharps Container 10.00 23.32 252.63	1.4L Metal Sharps Container	10	23.32	252.63	ICS 26L Flick Sanitary Disposal Units 27.00 6.89 201.53
575	Clinical Waste 19.00 41.34 850.91	Clinical Waste	19	41.34	850.91	1.4L Metal Sharps Container 10.00 23.32 252.63
579	Blewitt 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

RecordID	Data	Bin Type Description	Qty	Price	Total	Comments
569	Servicing
570	ICS 26L Flick Sanitary Disposal Units 27.00 6.89 201.53	ICS 26L Flick Sanitary Disposal Units	27	6.89	201.53
572	1.4L Metal Sharps Container 10.00 23.32 252.63	1.4L Metal Sharps Container	10	23.32	252.63
575	Clinical Waste 19.00 41.34 850.91	Clinical Waste	19	41.34	850.91	1.4L Metal Sharps Container 10.00 23.32 252.63
579	Blewitt Springs Fine Eggs Pty

While I want:

RecordID	Data	Bin Type Description	Qty	Price	Total	Comments
569	Servicing
570	ICS 26L Flick Sanitary Disposal Units 27.00 6.89 201.53	ICS 26L Flick Sanitary Disposal Units	27	6.89	201.53	Servicing
572	1.4L Metal Sharps Container 10.00 23.32 252.63	1.4L Metal Sharps Container	10	23.32	252.63
575	Clinical Waste 19.00 41.34 850.91	Clinical Waste	19	41.34	850.91
579	Blewitt Springs Fine Eggs Pty

How can I get the multi-row formula correct?

Thanks

[Ben_H]

Hi @HW1,

Give this a go -

IF !ISNULL([Bin Type Description]) AND ISNULL([Row-1:Bin Type Description])

THEN [Row-1:Data]

ELSE NULL()

ENDIF

It has to check that both the current row IS NOT null and the previous row IS null to return a value.

Regards,

Ben

[KarolinaRoza]

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

[apathetichell]

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

[KarolinaRoza]

Thanks @apathetichell I was not aware .

Karolina