Alteryx designer Discussions

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

Cross Tab Tool with Empty Rows as Delimiter

Highlighted
Atom

First post here, so hopefully I explain myself well enough! 

 

I have a data set of different records with their associated addresses in the format below, all in one field. Each record has a blank row in between. I'm guessing I need to use the cross tab tool in some way, but wasn't sure of how to set up the parameters for it. I've looked elsewhere to see if anyone else has had this issue and haven't had any luck.

 

Company1

Fullname1

Address1

 

Company2

Fullname2

Address2

 

I'm hoping to reformat these records using that blank row as a delimiter to show:

 

COMPANY     FULLNAME     ADDRESS

Company1     Fullname1        Address1

Company2     Fullname2        Address2

 

Thanks!

Highlighted
ACE Emeritus
ACE Emeritus

Hi @JakeW

You can use a multi-row formula to "rank" the records by incrementing when you see a blank row; then use that new field as your anchor for cross-tab.

Hope that helps!

John

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @JakeW,

 

Happy 1st post.  Here's what I suggest:

 

1.  Use a multi-row formula tool to create a "FIELD" (V_String) 64 with a formula of:

IF ISNULL([Row-1:Field]) or ISEMPTY([Row-1:Field1]) THEN 'Company'	ELSEIF
[Row-1:Field] == 'Company' THEN 'FullName'
ELSEIF
[Row-1:Field] == 'FullName' THEN 'Addresss'
Else Null()
ENDIF

This will now give you a label besides your data.

2.  Filter to remove the NULL records

!ISNULL(FIELD)

3. The trick is now to crosstab the data into sets of 3.  You need to add a recordID that increments when the field value = Company.

IIF([Field]='Company',[Row-1:RecordID]+1,[Row-1:RecordID])

Now you are ready to cross tab the data by grouping on the RecordID and setting the Header to FIELD and the Values to the name of your data column (e.g. Field1).

 

Cheers,

Mark

 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
Atom

@Magnetar and @MarqueeCrew

 

Thank you both for your quick input! I actually used a combination of both your suggestions. I wasn't totally clear on my question as we have some cases in the stacked column where:

 

Company1

Fullname1

Address1

Address1A (Suite or PO Box)

 

So there was a variance in the groupings. I ended up using a Multi-Row Formula to start, creating an INDEX field to "rank" each row that isn't null:

IF !ISNULL([FIELD1]) THEN [Row-1:INDEX]+1 ELSE 0 ENDIF

Then I filtered out the Null values.

 

I then, used another Multi-Row Formula to create a RecordID field to "group" the data based on the INDEX field:

IF [INDEX]=1 THEN [Row-1:RecordID]+1 ELSE [Row-1:RecordID] ENDIF

Finally I used the Cross Tab tool to Group Data by RecordID. New Column Headers were the INDEX field, and the Values for New Columns was the data field or "FIELD1". 

 

Thanks again for the help!!!

 

Labels