community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
We will be upgrading the Gallery to our 2019.4 release this Saturday, December 7th beginning at 9:00am MT. We expect the outage to take last approx. 2.5 hours.

Join multiple tables 2 at a time

Highlighted

Hi,

 

I want to create a time series data set.

I have data for each month in an year 

 

2019_11==>A 

2019_10==>B

2019_09==>C

 

I want to create a left join A&B , then left join it to C.

 

I need to do that using macro as the input data set might change and I will have to rename columns in the process.

 

Any idea how can I achieve this.

 

Thanks,
Ashish

 

 

 

 

 

 

Alteryx
Alteryx

Hi Ashish,

 

Rather than joining, wouldn’t it be better to standardize the columns and then use a union tool? If you have time series data Alteryx tends to like it better when it is in vertical format with a row for every month in your case. If you have months in different columns you may need to use a transpose tool in order to get it in the right format prior to union. If you can provide a sample data set it will be easier to mock up a workflow. 

Hi Brandon,


Thanks for getting back to me, I will share them mock up data soon!

 

Thanks,

Ashish

The data is of this kind

 

table mortgages_2019_11

 

MORT_NO  PAYMENTS_MISSED BALANCE

100               1                                 900

200               1                                 900

300               1                                 900

 

 

 

 

table mortgages_2019_10

 

MORT_NO  PAYMENTS_MISSED BALANCE

100               1.5                                 900

200               3                                 900

300               2                                 900

 

 

 

 

 

table mortgages_2019_19

 

MORT_NO  PAYMENTS_MISSED BALANCE

100               0                                900

300               2                                 900

 

 

What I am trying to do is join 2019_11 left join 2019_10

 

output would be 

 

MORT_NO PAYMENTS_MISSED_2019_11  BALANCE_2019_11 PAYMENTS_MISSED_2019_10  BALANCE_2019_10

100               1.5                                              900                               1.5                                            900

200               3                                                 900                               3                                               900

300               2                                                 900                                2                                             900

 

 

The above output joins to 2009_09

 

like that fashion.

 

 

The reason I want that in a macro is that number of months could wary and the data sets are huge so it may give memory error .

 

Thanks,
Ashish

 

 

 

 

 

 

Alteryx
Alteryx

I second @BrandonB 's approach of using a union rather than a join for this purpose. 

 

In addition you can use the Dynamic Rename function to rename the fields automatically, rather than manually renaming each column individually prior to the union. If the columns are always in the same order, you can use a single column with all of the field names in order from left to right. Alternatively, if the fields are not always in the same order, you can create a "mapping table" with the field names you'd like to use in one column, and the field names present in each of the input files in separate columns. See attached for an example using the first method. 

 

echuong1_0-1575383464152.png

Hi,

 

Please find the my workflow attached.I think I am very close to what I need but need to update the file name in the macro for very iteration.

 

e.g.

in the iteration 1 ==>join has to between 2009 and 2008

iteration 2 ==>output of iteration 1 and 2007 

and so on...

 

In my scenario I have for than 5 years of data  and the data size if huge so cant do it manually.

 

Thanks,
Ashish

Labels