How do i eliminate the bottom half of this data. Attached sample outcome Today Date[Null][Null][Null][Null][Null]Postion Rec[Null][Null][Null][Null][Null]Loans Active[Null][Null][Null][Null][Null]Bank NameCCYTenurreYearAmount RateHDFCINR520105,00,00,0005IDFC INR520215,00,00,0004SBIINR10202235,00,00,0005[Null][Null][Null][Null][Null][Null][Null]Total[Null][Null]45,00,00,000[Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null]Loans Repaid[Null][Null][Null][Null][Null]Bank NameCCYTenurreYearAmount RateIndBankINR5200815,00,00,0003IDFC INR520092,00,00,0005BOIINR10200915,00,00,0003[Null][Null][Null][Null][Null][Null][Null]Total[Null][Null]32,00,00,000[Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null][Null] Excluding Data.xlsx

Thank you Raj - Please can i ask your assistance in the below RecordIDTypeCashAccountAmount1HDFCPrin110000002HDFCInterest5000003SBIPrin12000004SBIInterest400000 Output HDFCSBIHDFC-SBIPrin1100000012000009800000Interest500000400000100000

Selecting Specific Rows from the Dataset

EmilAlteryx01

How do i eliminate the bottom half of this data. Attached sample outcome

Today Date	[Null]	[Null]	[Null]	[Null]	[Null]
Postion Rec	[Null]	[Null]	[Null]	[Null]	[Null]
Loans Active	[Null]	[Null]	[Null]	[Null]	[Null]
Bank Name	CCY	Tenurre	Year	Amount	Rate
HDFC	INR	5	2010	5,00,00,000	5
IDFC	INR	5	2021	5,00,00,000	4
SBI	INR	10	2022	35,00,00,000	5
[Null]	[Null]	[Null]	[Null]	[Null]	[Null]
[Null]	Total	[Null]	[Null]	45,00,00,000	[Null]
[Null]	[Null]	[Null]	[Null]	[Null]	[Null]
[Null]	[Null]	[Null]	[Null]	[Null]	[Null]
[Null]	[Null]	[Null]	[Null]	[Null]	[Null]
[Null]	[Null]	[Null]	[Null]	[Null]	[Null]
Loans Repaid	[Null]	[Null]	[Null]	[Null]	[Null]
Bank Name	CCY	Tenurre	Year	Amount	Rate
IndBank	INR	5	2008	15,00,00,000	3
IDFC	INR	5	2009	2,00,00,000	5
BOI	INR	10	2009	15,00,00,000	3
[Null]	[Null]	[Null]	[Null]	[Null]	[Null]
[Null]	Total	[Null]	[Null]	32,00,00,000	[Null]
[Null]	[Null]	[Null]	[Null]	[Null]	[Null]
[Null]	[Null]	[Null]	[Null]	[Null]	[Null]
[Null]	[Null]	[Null]	[Null]	[Null]	[Null]
[Null]	[Null]	[Null]	[Null]	[Null]	[Null]
[Null]	[Null]	[Null]	[Null]	[Null]	[Null]

Excluding Data.xlsx

Datasets

Output

Custom Tools

Help

Data Investigation

Accepted answers

All comments

cjaneczko

In your Input tool, start the import on Line 4. Then filter where year is Greater than or equal to 2010. Use a Select tool to uncheck any other unwanted fields.

EmilAlteryx01

I can use the greater than or equal to 2010 - because i have multiple rows and the Year is not unique

EmilAlteryx01

I want a solution to give me an output till here

Today Date	[Null]	[Null]	[Null]	[Null]	[Null]
Postion Rec	[Null]	[Null]	[Null]	[Null]	[Null]
Loans Active	[Null]	[Null]	[Null]	[Null]	[Null]
Bank Name	CCY	Tenurre	Year	Amount	Rate
HDFC	INR	5	2010	5,00,00,000	5
IDFC	INR	5	2021	5,00,00,000	4
SBI	INR	10	2022	35,00,00,000	5
[Null]	[Null]	[Null]	[Null]	[Null]	[Null]
[Null]	Total	[Null]	[Null]	45,00,00,000	[Null

cjaneczko

Add a Record ID tool then add a filter to filter out any Record ID's Greater than the line you want to cut off.

BRRLL99

You can try this Approach

Step 1 :

Add data cleaning tool

select remove Null rows

This will remove all Null rows

Step: 2

you need only data which is Active

create new field which is Active

Add formula tool create new column

add this formula :

IF Contains([Field1], "Loans Active")
then 'Active'
elseif contains([Field1], 'Loans Repaid')
then 'repaid'
else ''
endif

Step : 3

Add multi row formula tool, update existing new field

IF ISEMPTY([ACTIVE])
THEN [Row-1:ACTIVE]
ELSE [ACTIVE]
ENDIF

Step : 4

Use Filter

add this formula
[ACTIVE] = "Active" AND
[Field1] IN ('Bank Name', 'HDFC', 'IDFC' ,'SBI')

This approach will make your workflow dynamic

EmilAlteryx01

Step 1 - Accpeted

Today Date
Postion Rec
Loans Active
Bank Name	CCY	Tenurre	Year	Amount	Rate
HDFC	INR	5	2010	5,00,00,000	5
IDFC	INR	5	2021	5,00,00,000	4
SBI	INR	10	2022	35,00,00,000	5
[Null]	Total	[Null]	[Null]	45,00,00,000	[Null]
Loans Repaid	[Null]	[Null]	[Null]	[Null]	[Null]
Bank Name	CCY	Tenurre	Year	Amount	Rate
IndBank	INR	5	2008	15,00,00,000	3
IDFC	INR	5	2009	2,00,00,000	5
BOI	INR	10	2009	15,00,00,000	3
[Null]	Total	[Null]	[Null]	32,00,00,000	[Null]

EmilAlteryx01

This is basically what i think you suggested in Step 2 (which i agree) - but the formulae suggested on Step 2 doesn't work as Loans Action is only on the First cell and Loans Repaid is on a separate line (Row data change daily)

Loans Active						Loans Active
Bank Name	CCY	Tenurre	Year	Amount	Rate	Loans Active
HDFC	INR	5	2010	5,00,00,000	5	Loans Active
IDFC	INR	5	2021	5,00,00,000	4	Loans Active
SBI	INR	10	2022	35,00,00,000	5	Loans Active
[Null]	Total	[Null]	[Null]	45,00,00,000	[Null]	Loans Active
Loans Repaid	[Null]	[Null]	[Null]	[Null]	[Null]	Loans Repaid
Bank Name	CCY	Tenurre	Year	Amount	Rate	Loans Repaid
IndBank	INR	5	2008	15,00,00,000	3	Loans Repaid
IDFC	INR	5	2009	2,00,00,000	5	Loans Repaid
BOI	INR	10	2009	15,00,00,000	3	Loans Repaid
[Null]	Total	[Null]	[Null]	32,00,00,000	[Null]	Loans Repaid