We have a Database with 3 name fields and weâd like to construct a query that searches any name across all fields and returns the record for the selected name:
|
Name 1 |
Name 2 |
Name 3 |
|
Mark |
Jim |
Brett |
|
Seth |
Tara |
Amy |
|
Margie |
Lonnie |
Bob |
We'll Load the database into the Calgary Loader and create the database in indexes.
When creating the Raw Index, stream the database into a RecordID tool and make the starting value 0. This identifies the first record in the database as 0 (or a 0based index).
Transpose the data to align all names in 1 column with the 0based index in the same row:
|
RecordID |
Value |
|
0 |
Mark |
|
0 |
Jim |
|
0 |
Brett |
|
1 |
Seth |
|
1 |
Tara |
|
1 |
Amy |
|
2 |
Margie |
|
2 |
Lonnie |
|
2 |
Bob |
Feed the results of #3 into the Calgary Loader and configure it as shown below:
When you then query using the Calgary Input tool, you can select any name and return the record it resides in. The tool knows that "Brett" is located in Record 0.
©2018 Alteryx, Inc., all rights reserved. Allocate®, Alteryx®, Guzzler®, and Solocast® are registered trademarks of Alteryx, Inc.
Related Topics