Alteryx joins

Hi All,

I have 2 fields ( user name and user address in 1 table) and I have created another table after appending data from 32 text files containing data of different categories (e.g banks, loans, insurance) and that table would contain 2 fields ( 1st is data_field containing values ( from text files)) and flag field to identify from which text file the data is coming.

Now my aim is to create 2 new fields category and sub_category which would be based on below logic:

if (findstring(uppercase(username), data_field)>= 0 or findstring(uppercase(useraddress), data_field)>= 0) and flag = 'Bank'

THEN CATEGORY = 'Banking' and sub_category = 'Cards';

elseif ....

Now as there is no matching field to join the 2 tables and also with findreplace tool I can match only 1 field ( or part of field), so I am not able to fix this issue.

Below mentioned is sas code which I am trying to migrate to alteryx.

here file are various text files on the basis of which I am creating macro variables and using in condtions:

%macro string(file);

%global dd_&file.;

proc sql noprint;

select "index(upcase(bacs_user_nam),'"||trim(string)||"') or index(upcase(adr_l1),'"||trim(string)||"')"

into :dd_&file. separated by " or "

from &file.;

quit;

%mend;

%string(Banks);

data dd_categorisation;

set lookup.test;

if &dd_fuelcard. then do;subcat='Fuel Card';category='Cards';end;

else if &dd_buscard. then do;subcat='Business Card';category='Cards';end;

else if &dd_perscard. then do;subcat='Personal Card';category='Cards';

---

run;

Developer

Reporting

Accepted answers

danilang

Hi @vikas1

Here's one way you could do it.

The User Table contains the user info, Appended Data contains the appended contents of your 32 text files and Flags to Category mappings contains the logic that you coded in the If statement in your original SaS code. I've broken the process down into 2 chunks. The first container finds the correct flag by transposing the username and address fields so that they are in one column. The Find Replace tool works with the one transposed column and appends the flag to the row. After removing the blanks, the flags are joined back to the input data on UserID. The second container appends the category/subcategory fields based on the flag. By putting the flag to category mappings in a separate input, you can now modify the logic of your original IF statement without having to change the code in the workflow

Here are the results

Dan

FlagToCategory.yxmd

danilang

Hi @vikas1

Here's the macro

It's basically just the Find Replace and the following Filter from v1 of the workflow. The 1st filter is what makes the macro work. The equation here is

[ProcessingOrder] = [Engine.IterationNumber]+1

Engine.IterationNumber increments by 1 on each iteration of the macro. This filter does the find replace for the file with ProcessingOrder =1 on the 1st iteration, ProcessingOrder=2 on the second, etc. until there are no more files to process. The results of all the iterations are unioned before being returned to the calling workflow.

Dan

All comments

ashissanpui

It would be helpful to visualize if you add sample data.

danilang

Hi @vikas1

Here's one way you could do it.

Here are the results

Dan

FlagToCategory.yxmd

vikas1

Thank you so much Dan for this solution.

Only problem I am facing now is that in case search words in multiple text files are same e.g 'ASSURED' word is common in insurance as well as banking text files and when I am appending these files to create appended table,

then findstring(USER_NAME,'ASSURED')>= 0 , then it is getting flagged incorrectly in few cases. ( should be flagged as insurance) but getting flagged as banking and returning incorrect result.

here user name is : ASSURED ENVIRON

vikas1

Hi Dan, I am not able to categorize it correctly in alteryx because if I create flag for each text file, then it is checking for the first instance in find replace tool.

Below mentioned is sas code:

data yy;

set lookup.xx;

format subcat $32.;

format category $32.;

if &pod_insurance. then do;subcat='Insurance';category='Insurance';

else if &pod_banks. then category='Banks/Building Societies';

……..

else '';

run;

in my search string, I have LYODS in banks text file and INSURANCE in insurance text file.

when I am using findreplace tool on 'LYODS TSB INSURANCE' then this string should be categorized as 'Insurance'

as this is the value in pod_insurance sas macro and we are searching this macro before pod_banks, but in alteryx it is getting categorized as banks as LYODS is the first word in string.

Can you please tell me any other way to fix this issue so that I can categorized it correctly.

Thanks in advance.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703