cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Discover peer insights or crowdsource your one of a kind Designer question.

Tool Mastery | Join Multiple

Meteoroid

Hi, I am trying to join two files but have a unique requirement. I tried all sorts of joins but nothing gives me the desired result, hoping to pick some brains: 

File A                    FileB                                I need

GPI1 Price1           GPI2 PRIC2E LIST                  GPI1        Price1 GPI2 PRICE2    List

A       1                A    1         List1                           A              1          A       1              List1

B       2                B    6          List2                         B               2         B       6              List1

C      3                 A    7          List1                         C               3        null    null            List1

                                                                              A                1        A     7                 list2

                                                                              B                2       null  null              list2

                                                                              C                3       null  null              list3

 

I cannot add list column for fileA, since the file gets massive. Tried bunch of joins did not work. Tried append files did not work. The easiest solution for me would be to find a specific combination for this to work, or if there is a way to repeat the data with in alteryx for file A meaning add lines A,B,C ffor list 1 and at the bottom add again A,B,C for list 2 and then join on GPIANDLIST for file A and file B?

Response is ,much appreciated. Thanks

                                                                              

Alteryx
Alteryx

Hi @shashidhar1,

 

Could you attach a sample file (or files) with the data you're trying to join? It's a little hard to parse what you're trying to do with the text directly pasted into the discussion.

 

Thanks!

Meteoroid

Please find the attached,

 

Would like to join File A,File B to get the desired result. All attached in different tabs of the attached spreadsheet.

 

Leftjoin would not work because entry D will only be populated once, but i need it for MAC1 and MAC2.

 

Basically FileA, is my master price table, File B has prices for different list MAC1,MAC2 etc... I have to updated file B for any new entries in File (A,B,C) and delete entries from file b which are not in the current master price table. This should happen for all list MAC1,MAC2 etcc. i then output MAC1,MAC2,MAC3 as different sheets in same workbook. Hope this clears it. 

 

Appreciate the response. Thank You

Alteryx
Alteryx

Thanks!

 

So, first thought is that you want a full outer join between these two datasets based on the GPI column. You can do this by using the Join tool followed by a Union tool which has all three Join tool outputs connected to it. 

I'm still not clear on which dataset should supersede the other. I think what you're saying is you want to update the prices from File B with the prices from File A, when they exist on both sides, but I'm not sure. In any case, once you see the output of the full outer join, I think your next steps may become more clear. 

The way to output the new data to new sheets in the original xlsx file is to use the List field's data (MAC1, MAC2, etc.) to generate a dynamic output path at runtime using a Formula tool and the options at the bottom of the Output Data tool's config pane.

I've attached an example workflow for your reference with some of these things illustrated. Let me know if I've got it wrong here, or if you need any additional help!

 

Cheers!

Meteoroid

Hey,

Thank you so much for all the help.

You are right, if the File B has the GPI from File A then do not change them, If file B has GPI not on file A delete them, If file B does not have the GPI from File A add them. The left join would not work because for a new entry say D, it only bring one row with nulls, i want D to be added to MAC1,MAC2 as well.

I cannot open your file since my version is an older version. But see below the results if i do left outer join.

image.png

I need to add D, to both MAC1 and MAC2, The join does .not help me If i got two entries for MAC1 and MAC2 i would copy price

Alteryx
Alteryx

Aww man. Sorry about that. I modified my yxmd to look like a version 10.0 file and reattached.

 

Fun tip: You can modify the version of a workflow file too, if you ever need to, by opening up the offending yxmd file in any text editor (like notepad) and simply modifying the second line which should look something like  '<AlteryxDocument yxmdVer="11.7">'. I just changed that '11.7' value to '10.0'. Now, any version of Designer >= v10.0 will open it without complaining. Your mileage may vary when trying to open workflows created in a newer version of Designer in older versions, especially if the newer workflow uses features which did not exist in older versions of Designer, but this will fix you for a large percentage of workflows.

 

Let me know if you still have issues using the attached yxzp's contents.

 

Alteryx
Alteryx

You should be able to use the data from a full outer join along with some filtering and some formula expressions to fill in the missing data that you need. Let me know if you'd like an example of what I mean by that. I'll wait to hear back from you to avoid going down the wrong path, here. 

Alteryx
Alteryx

Also, I just noticed I forgot to change the output method of that Output Data tool for subsequent outputs after the first. Change that Output Data config's 3rd line (Output Options) to 'Overwrite Sheet (Drop)'. Like the screengrab below:

2018-03-13_13-18-20.png

Meteoroid

Hey Thank you very much for the consistent help.

 

I could not open your workflow, after i import an empty alteryx flows comes up. Anyway, i figured out the logic and trail and error i found an efficient method for my problem.

 

Now, i am facing an specify sheet name error in the ouput. I had the same error yesterday and i did solve it but i forgot how. Can you help me with that please and my output setting is as followsimage.png

Alteryx
Alteryx

If MACLIST is ever null, you might have issues. If that's the case, you can just do an iif(IsEmpty(MACLIST), '(No List Name)', [MACLIST]) formula expression to give them a value like '(No List Name)'. You could also accomplish this with the find and replace tool with a simple text input to create your find/replace mappings.

If that's not your problem, I usually have a better time using the 'Change Entire File Path' output option (at the bottom) and manually generate my full filename in a formula expression which is then used for each record dynamically. The expression to do this would look something like '.\MyFileName.xlsx|' + [FieldToUseAsSheetName] and the generated path would look something like '.\MyFileName.xlsx|MySheetName'. Then you just use that field in the Output Data config in that bottom-most field. I usually uncheck the box to 'Keep Field in Output', since its only use is generally to control the output flow.

 

If none of these things seem to help, see if you can export your project to a yxzp and send that to me. Maybe I can figure out why you're unable to open my workflows by looking at yours. The export option can be found under the Options menu, in the main menu bar, in Designer. Default options should work fine, but you can change your output path by modifying the Package Location field, which the red arrow calls out, below.

 

2018-03-13_14-00-08.png