Hello, I have a data stream like below ABCDEF487455496637315243411652112687144181541092453458165345514552618114228072665663644665852864210823125445041820945368847522196425434623700287557212169280255981755474835494819868490234406156319469846755685652751204243922972036122857013111195176863537574835824634826811137718871840950139750753834965737284907462224917730567765459718720221458149935162620547178654634667411342631672562353251696931512985942273011434581174032123866013831935128958112774166050273675756575667432422 And my objective is to check that if Column A has data from below - A92332350821866448223396088505370430354561135191126011397854794505941408829377436638717539111470670885683988512611729521578322022291 So, data stream 1 has those 6 fields and data stream 2 has just one field A. My objective is to check if any cell in Column A of Stream 1 has any of the values that Column A has in Stream 2. Also, Data Stream 2 is static and I can hard code it using a Text Input tool in the workflow. And Data Stream 1 is coming from a SQL Query. I tried doing this using Append/Union/Join but could not do. Any guidance will be much appreciated. Thanks so much.

Filtering one data stream based on a list of values from another data stream

Hello,

I have a data stream like below

A	B	C	D	E	F
48	745	549	663	731	524
341	165	211	268	714	418
154	109	245	34	581	653
455	145	526	181	142	280
726	656	636	446	658	528
642	108	231	254	450	418
209	453	688	47	522	196
425	434	623	700	287	557
212	169	280	255	98	175
547	483	549	48	198	684
90	234	406	156	319	469
84	675	568	565	275	120
424	392	297	203	612	285
701	311	119	517	68	635
375	748	358	246	348	268
111	377	188	718	409	501
397	507	538	349	657	372
84	90	74	622	249	177
305	677	654	597	187	202
214	581	499	351	626	205
47	178	654	63	466	741
134	263	167	256	235	325
169	693	151	298	594	227
301	143	458	117	403	212
386	60	138	319	351	289
581	127	741	660	502	736
757	565	756	67	432	422

And my objective is to check that if Column A has data from below -

A
9233
23508
21866
44822
33960
8850
53704
30354
56113
51911
26011
39785
4794
50594
14088
29377
43663
87175
3911
14706
70885
68398
85126
11729
52157
83220
22291

So, data stream 1 has those 6 fields and data stream 2 has just one field A. My objective is to check if any cell in Column A of Stream 1 has any of the values that Column A has in Stream 2. Also, Data Stream 2 is static and I can hard code it using a Text Input tool in the workflow. And Data Stream 1 is coming from a SQL Query.

I tried doing this using Append/Union/Join but could not do.

Any guidance will be much appreciated.

Thanks so much.

Workflow

Input

Tips and Tricks

Parse

Help

Accepted answers

aatalai

@Gandalf_NotGrey would something like this help

stream filter.PNG

Filtering one data stream based on a list of values from another data stream.yxmd

All comments

Hub119

A join tool within Alteryx should accomplish what you are looking to do here... in your sample dataset provided at least, nothing matches between the two sources which perhaps is the source of the issue you are encountering?

aatalai

@Gandalf_NotGrey would something like this help

stream filter.PNG

Filtering one data stream based on a list of values from another data stream.yxmd

Hub119

...this of course assumes you are looking to do an exact match on your two data streams. If instead you were looking to see if the data just contained something from data stream 2 (say for instance you were looking to see if a name or an ID string showed up as part of a larger string of data) then you could use an append fields tool first, to append the full stream 2 dataset to all incoming stream 1 records, and then follow that with a filter tool to filter down to only records where stream 1 column A contained the string within the stream 2 column A using a contextual formula like this: "Contains([A], [Right_A])" ....fyi needs to be string type data for that to work.

Mathias_Nielsen

Hi @Gandalf_NotGrey ,

Here is two different solutions to what you are looking to do. One is looking for an exact match, the alternative will look for any value containing a value in data stream 2

New Workflow13.yxmd

Mathias_Nielsen

The alternative solution is similar to what @Hub119 is describing

Gandalf_NotGrey

Thanks so much @Mathias_Nielsen @Hub119 @aatalai for your suggestions. Join tool is working well for my case.

Yes, to do the Contains part, what is happening is my Append was leading a lot more records since both Target and source had many records.

So I modified my data in such a way that a Join can work. But yes, Appending the data and doing Contains was my first thought as well.

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335