Alteryx joins

[vikas1]

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;

[ashissanpui]

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

[danilang · Accepted solution]

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

[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.

[danilang]

Hi @vikas1 

I added in the concept of file processing order with 1 being processed first, then 2, etc.  This is controlled by the input called Processing Order.  In this input, I set Insurance to 1, Banking to 2, etc. This is to replicate the if--then--else if structure from your sas code.  Since we now need to perform multiple passes through the Find Replace tool, I moved it into a macro.  The macro runs through each of the Flags in Appended Data in turn by processing order.  All the matched records are returned and the ones with lowest processing order are selected effectively assigning LYODS to Insurance as opposed to BANKING

[vikas1]

Hi  @danilang ,

can you please share the macro? I am getting confused as to how to use it as for each flag ( e.g. banks)there are around 10 values and there are 32 such text files.

So in total, there would be around 32 flags and 320 data values in the appended data file that I would be checking.  and if create 2 columns( flag and processing order) in the processing order file and join it on flag with appended data file, then how I would be using it in findreplace tool ( macro).

Thanks for your support on this..

Regards,

Vikas  

[vikas1]

Hi @danilang ,

Actually, I cant open this macro in my laptop as it is office laptop, that's why request you to please share the picture of macro and what exaclt your doing inside macro.

Thanks.

[vikas1]

Hi @danilang ,

Can you please share the screenshot of the macro you have used and what you have done inside macro.

Thanks for your help.

[danilang · Accepted solution]

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