community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Selecting Records Between field values

Highlighted

Hi All,

 

I have an excel which has data generated every month as shown below,

 

    
    
Central   
STATEIDNameTotal
IL123Spencer56
AZ456Stan67
>40 Total encounters   
    
Central MAT   
STATEIDNameTotal
IL890Drake98
LA64Holston105
>50 Total encounters   

 

 

As you can see, there 2 tables within the same sheet 'Central' and 'Central MAT' and the corresponding data follows. I would like to separate these data, where Central table will have 2 lines(Data between Central header and >40 Encounters) and Central MAT with 2 lines(Data between Central MAT header and >50 Encounters). This data is dynamic monthly and hence the process of extracting the data between the tables needs to be dynamic as well.

 

Alteryx
Alteryx

Hi @santosh_garimella,

 

The final solution would depend on how you want tot use the data. A couple of things to get you started though:

 

Configure the Input Tool to not read the first line as headers

Use a Multi-row Formula tool to identify the tables 

IF StartsWith([Field1],'Central')
THEN [Row-1:TableNumber]+1
ELSE [Row-1:TableNumber]
ENDIF

You can then split on the tables

Possibly another Multi-Row Formula tool to number the lines of the table grouping on TableNumber.

[Row-1:LineNumber]+1

You could then filter out everything except rows 2-4 for instance.

 

Kane

Labels