topic Re: Extract text data in Alteryx Designer Desktop Discussions

Extract text data

lizhaozhi — Wed, 15 Mar 2023 07:30:13 GMT

Hello,

I have a text file with the following structure. I want to extract everything in Name List B. What can I do? I was looking at multi-row formula to fill down some flags than use filter to remove "remove" tagged rows, but not sure how to do it.

Input

- - - - - - - - - - - - - - - - - - -

Date: 2023-03-15

Name List A

- - - - - - - - - - - - - - - - - - -

First Name | Last Name

XXX | XXX

- - - - - - - - - - - - - - - - - - -

Date: 2023-03-15

Name List B

- - - - - - - - - - - - - - - - - - -

First Name | Last Name

XXX | XXX

- - - - - - - - - - - - - - - - - - -

Date: 2023-03-15

Name List C

- - - - - - - - - - - - - - - - - - -

First Name | Last Name

XXX | XXX

- - - - - - - - - - - - - - - - - - -

Output:

First Name | Last Name

XXX | XXX

These should be names starting below the row Name List B. I don't want anything else in the output.

Thanks,

Alteryxer

Re: Extract text data

ShankerV — Wed, 15 Mar 2023 07:50:01 GMT

Hi @lizhaozhi

One way of doing this.

Many thanks

Shanker V

Re: Extract text data

ShankerV — Wed, 15 Mar 2023 07:53:29 GMT

Hi @lizhaozhi

Step 1: Input

Step 2;

IF [Field1]="Name List B"
THEN 100
ELSE [Row-1:Temp]+1
ENDIF

Step 3:

[Temp] >= 102 AND [Temp] <= 104

Many thanks

Shanker V

Re: Extract text data

lizhaozhi — Wed, 15 Mar 2023 07:58:13 GMT

@ShankerV Thanks for helping. Please note that the example I give is just a representation. There could be different number of names in each name list. Therefore, I'm afraid hardcoding [Temp] <= 104 cannot achieve full automation.

Re: Extract text data

ShankerV — Wed, 15 Mar 2023 08:10:48 GMT

Hi @lizhaozhi

I believe the line

------------- will be available in the input right.

Many thanks

Shanker V

Re: Extract text data

lizhaozhi — Wed, 15 Mar 2023 08:20:38 GMT

Yes. The dashed lines are in the file as well. It's just that there could be two rows of names in Name List A, five rows of names in Names List B, and one row of name in Name List C, etc. I want this to achieve full automation so that no matter this variance, I can use this configuration to always extract every name rows in Name List B.

Re: Extract text data

ShankerV — Wed, 15 Mar 2023 08:23:03 GMT

Hi @lizhaozhi

Here is full automation even if we have 4 lines, 5 lines or n number of lines.

Many thanks

Shanker V

Re: Extract text data

ShankerV — Wed, 15 Mar 2023 08:25:05 GMT

Hi @lizhaozhi

Step 1: Input

Step 2:

Step 3:

IF [Field1]="Name List B"
THEN 100
ELSE null()
ENDIF

Many thanks

Shanker V

Re: Extract text data

ShankerV — Wed, 15 Mar 2023 08:27:19 GMT

Hi @lizhaozhi

Step 4: Multi row formula

IF !Isnull([Row-1:Temp]) and contains([Field1],"- - -")
THEN 2
ELSEIF [RecordID]>4 and [Row-1:Temp1]=2 and !contains([Field1],"- - -")
THEN [Row-1:Temp1]
ELSE null()
ENDIF

Step 5;

Step 6:

Hope this helps!!!!

Many thanks

Shanker V

Re: Extract text data

Christina_H — Wed, 15 Mar 2023 10:00:00 GMT

Here's a way to do it with a single multi-row formula.