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
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
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
@KarolinaRozaFYI - ISEMPTY() flags null values as well so you don't need both - just the !ISEMPTY combo.