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

Alteryx designer Discussions

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

Add an Hlookup row below field headers

Highlighted
Meteoroid

Hi, 

 

I am trying to manipulate a data set for reporting and I am having a difficult time cross tab-ing the data. Below is an example of the data files:

 

Table 1 includes the threshold for each fund, and Table 2 includes the balance of three unique investments per fund and an evaluation of whether the investment is significant to the fund if the balance of the investment exceeds the fund threshold. 

 

Table 1 
FundThreshold
A5
B10
C15

 

Table 2    
FundInvestmentBalanceThresholdSignificant?
AX105Yes
AY105Yes
AZ05No
BX1510Yes
BY510No
BZ510No
CX2015Yes
CY015No
CZ015No

 

When I cross-tab each Fund as a separate field, my output looks like this due to the different thresholds per fund. 

 

Table 3     
InvestmentThresholdSignificantABC
X5Yes1000
Y5Yes1000
Z5No000
X10Yes0150
Y10No050
Z10No050
X15Yes0020
Y15No000
Z15No000

 

Ultimately, I am trying to add the fund threshold above the fund name for each column, such that the table is condensed in the following format (due to the large number of investments versus lower number of funds, this is the ideal reporting format). I can't cross tab the threshold amount as that would just create new columns with numeric headers. In excel, I would simply add a row and use a vlookup from my original table 1, but i cant figure out how to do that within Alteryx. Thank you! 

 

Final Table     
   51015
InvestmentBalanceSignificantABC
X45Yes101520
Y15Yes1050
Z5No050
Pulsar

This is probably not 100% what you're looking for, but maybe something you can work with.

 

I crosstab the 2 tables separately and then bring them together as report objects. 

 

In order to get the X,Y,Z rows, I grouped by Investment in the Crosstab tool. This means that you lose the Significant? column, but perhaps you can add it back in with a formula in the formula tool? If you group on Significant? as well, you get 2 rows for Y, as one has 'Yes' and the other 'No' in Significant?.

 

I added in dummy columns after the crosstab for the thresholds, otherwise it stretched A B and C across the whole width. Perhaps you can play around with something different here.

 

All this is provided that you are ok with the reporting object format as your output?

 

report.png

 

 

Nebula
Nebula

Hi @smoinpour

 

As usual, @DavidP has an excellent response, but your final table losses the significance of each investment to each fund.  This information is captured in your Table 2 but then lost in your final Table.  Correct me if i'm wrong, but you are interested in the significance of each investment to each fund, are you not?

 

This work flow transposes the information in table 2 into a format similar to your final table, i.e. funds as columns and investments as rows, but adds an extra column under each fund to show the significance of each investment to that particular fund.  So investment Y is significant to fund A but not to B or C.

 

output.png

 

The 1st 3 rows are header info and the balance are the data rows for each investment.  The odd numbered columns are the balance of each investment for the corresponding fund and the even numbered ones are the significance

 

The solution scales to any number of funds and investments as long as all the required info is in the 2 input tables.

 

Solution.png

 

 

Dan

Labels