AnsweredAssumed Answered

Best methods for querying variables?

Question asked by wedgeman on Nov 1, 2018
Latest reply on Nov 1, 2018 by philmodjunk

We've got a rather complex report, based on several thousand data points across 9 tables.

 

In order to best consolidate the data, we auto-built a series of variables (around 200 of them) which use relationships and/or SQL queries to compile.   Most of the variables are sequential (ie,. $$v1. ~ $$v25, etc), gathering similar but sequential data from numerous points.

 

The biggest challenge we're encountering is how to compile metadata from these variables.

 

So as an example, let's say that there are

     50 variables ($$vs1.1~vs1.51) which handle sales

     50 variables ($$vcg1.1~vcg1.51) which handle cost of goods

     50 variables ($$vpl1~$$vpl51) which calculate P & L for these 50 product groups

 

Each of these variables is actually a list of sales, (or cost, or profit) across 45 locations, so each variable contains delimited data of that product with 45 delimited lines of numbers, (integers for sales & CG, and decimal'd percentages for P&L - ie., ".3572").

 

We need to further condense these variable lists into 2 things:

     1. A metadata variable showing the P&L average for say, all $$vpl14 across its 45 locations (compiling all 45 returns)

     2. We need a P&L chart showing counts of p&l for products:

So, say of these 50 $$vpl's, we want to create a pie chart, showing three returns:

  1. count of products with margin lower than 10%
  2. count of products with margin 10%-25%
  3. count of products with margin greater than 25%

 

 

 

Questions:

 

A - How should I query a delimited variable for averages?

B - How should I query a variable (which is a list of percentages) for 1., 2., 3 above)?

C - Is there a method of searching a list for a value (ie., patterncount; variable; "<xxx%")?  I need to query that variable list for a valuecount of a range, not a specific number.   how would one pull that data?

Outcomes