Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Find and Return Next Two Rows (Batch Macro?)

rjkim222
5 - Atom

Hello!

 

First time poster here. I have two datasets:

 

Dataset 1 - Server Listing:

Server Name
Server A
Server B
Server C
Server D

 

Dataset - 2Server Type and Version:

Generic Header

Server A

Server A Type
Server A Version
Additional unrelated records
Server A Version
 
Server B
Server B Type
Server B Version
Additional unrelated records
 
Server D
Server D Type
Server D Version
Additional unrelated records

 

Imagined Results:

Server NameTypeVersion
Server AServer A TypeServer A Version
Server BServer B TypeServer B Version
Server Cnullnull
Server DServer D TypeServer DVersion

 

I want each record (server name) from Dataset 1 to be searched against Dataset 2. If Server Name is found in Dataset 2, I'm wanting to return the server type and server version in the next two rows in Dataset 2 as two columns next to the Server Name in Dataset 1.

 

I've been looking at different batch macro examples as I think it would fit this scenario, but can't find any that would return the next two rows in a separate dataset. All records in Dataset 2 are in one column and there are additional records that do not need a return after the first two rows.

 

Thanks for any help or examples I can be pointed to for assistance!

6 REPLIES 6
mattnason1
9 - Comet

Here's how I would do it (although I didn't set up as a macro).

Luke_C
17 - Castor

Hi @rjkim222 

 

No macro needed:

 

  1. I used a multi-row formula to group the 2nd data set based on the empty row separating servers
  2. Removed the empty rows and used a tile tool to number each field, then filtered to the first 3 rows for each server
  3. Cross tabbed the data and joined to the server names
  4. Unioned any fall outs to get your desired presentation.

Luke_C_0-1647545705619.png

 

 

mattnason1
9 - Comet

Tweaked mine to get to output.  But looks like Luke made a nice solution too.

rjkim222
5 - Atom

Thanks for spending time on my question. I'm seeing this as the final result when running the workflow:

rjkim222_0-1647546634422.png

 

I'm seeing where you were going with this one. Matching Server Names first, and then designating 1=true for the type and version if there was a match. What gets lost in the final output is the matching server name and then having them return as columns for each matched record.

 

 

Luke_C
17 - Castor

@rjkim222 I believe my solution above should get you there. Please check and let me know.

rjkim222
5 - Atom

Luke,

 

Thanks for taking your time for the write-up and the workflow. Got a couple useful things I can learn from this for future workflows in your expression and the use of the Tile Tool. Haven't seen that one used before.

Labels