Combining Data Items Across Queries

[ggustafson]

This is more of a general question than trying to solve a particular error, but I am working with a relational data source, and I have a project where we are attempting customer stratification; pulling in a variety of data related to our customers, calculating a letter grade for an assortment of metrics, and then combining those grades into a weighted model for a final grade for each customer.

Some of the calculations have to occur at the transactional level in different places; for example, I need to get Days to Pay for each transaction in our Accounts Receivable Transactions table, and also Average Order Lines, Average Order Size, and a variety of other metrics from our standard Invoice tables. The two tables have unique transaction IDs, so I'm not sure that I could join them in any way without getting exploded rows in my tabular data.

Is there a suggestion or best practice for trying to combine the results of different queries and be able to perform further calculations? Ideally, I would be able to determine these letter grades at a customer level in each query, and then join just those single line summaries together to calculate a final grade, but I wasn't sure about the best way to achieve this.

In the meantime, I'll keep fiddling with joins and unions to see if there is a way to get all the data into a single query that makes sense, but I appreciate any ideas or suggestions, or even any resources to review if there is an easier or better way to go about this.

Thank you.

[MFGF]

This is more of a general question than trying to solve a particular error, but I am working with a relational data source, and I have a project where we are attempting customer stratification; pulling in a variety of data related to our customers, calculating a letter grade for an assortment of metrics, and then combining those grades into a weighted model for a final grade for each customer.

Some of the calculations have to occur at the transactional level in different places; for example, I need to get Days to Pay for each transaction in our Accounts Receivable Transactions table, and also Average Order Lines, Average Order Size, and a variety of other metrics from our standard Invoice tables. The two tables have unique transaction IDs, so I'm not sure that I could join them in any way without getting exploded rows in my tabular data.

Is there a suggestion or best practice for trying to combine the results of different queries and be able to perform further calculations? Ideally, I would be able to determine these letter grades at a customer level in each query, and then join just those single line summaries together to calculate a final grade, but I wasn't sure about the best way to achieve this.

In the meantime, I'll keep fiddling with joins and unions to see if there is a way to get all the data into a single query that makes sense, but I appreciate any ideas or suggestions, or even any resources to review if there is an easier or better way to go about this.

Thank you.

Hi,

To be able to bring together data from different queries like this you'll either need to join the queries or union them. It sounds like joining is what you need, assuming you need the different values on the same row to use in calculations? If there are no common items between the queries to join on, the next best choice might be "position based" joining, where row 1 from the first query joins to row 1 from the second, row 2 from the first to row 2 from the second etc. You would need to generate row numbers within each query and join on these.

Cheers!

MF.