Order values from multiple columns and return arbitrary (1st, 2nd, 3rd) number

[robertp]

Hello everyone,

Please bear with me as I try to explain this, this is the first time I've run into a problem like this.  If I'm unclear about a part of the problem, I'll be happy to clarify anything needed.

I've been asked to make a report that requires being able to return an arbitrary ranked value from a row with many similar columns (the columns are sales quantities per location of items per month).  The 2nd highest value out of the 13 available, for example.  Another test requires the 4th highest.

Each item has a unique row for a location, but not all locations have sales histories (therefore some of the month column cells have nulls).

Please see the example table below:



   
   
   
   
   
   
   

Item	Location	Current Month	Prior Month	2 Months Ago	3 Months Ago	4 Months Ago	5 Months Ago	6 Months Ago	7 Months Ago	8 Months Ago	9 Months Ago	10 Months Ago	11 Months Ago	12 Months Ago
10001	01	4	10	3	7	6	9	2	1	8	4	5	6	7
10001	02	5	6	7	1	9	8	3	2	0	4	5	2	4
10001	03	0	0	0	4	5	8	1	6	4	2	3	7	1
20025	01	5	6	2	8	7	6	4	9	1	3	5	4	6
20025	02	2	5	6	9	8	4	5	6	3	0	0	5	4
20025	03	5	4	8	9	7	2	3	2	1	5	6	0	4

I think what I need to do is:

• Add the columns to a temporary list
• Order the temporary list
• Return the Nth item by selecting TOP N items and re-ordering again

Or I might be completely off base.  I'm still looking for how I would go about my first step, columns to a temporary list.  I'm looking for any advice anyone can give.

Thank you for your time and consideration.
Best regards,
Rob

[Lynn]

Relational or dimensional source?

[robertp]

It's a relational source (DB2).  I can deal with this table by itself for the time being, I'll only be joining in other tables for things like item descriptions.

[Lynn]

Can you get a table that has the data row-wise rather than column-wise? This means you'd have 13 rows for each 1 row you currently have.

If the table had columns for item, location, time frame, and sales then you could easily use the rank function with a scope "for" clause by item and location.

Sorry I'm not seeing an obvious way to achieve it with the data arranged in columns but maybe someone else clever will chime in with ideas.

[MFGF]

Can you get a table that has the data row-wise rather than column-wise? This means you'd have 13 rows for each 1 row you currently have.

If the table had columns for item, location, time frame, and sales then you could easily use the rank function with a scope "for" clause by item and location.

Sorry I'm not seeing an obvious way to achieve it with the data arranged in columns but maybe someone else clever will chime in with ideas.

Agreed - what we're seeing here looks like an array, and arrays don't mix well with relational databases. The first rule of normalization removes arrays, in fact, and relational databases are designed to work most optimally with normalized data.

If it was me, I'd be looking at pivoting the data to a row-based data set, then build a report over this. It would be much easier to do, as databases and reporting tools are designed to handle it easily.

Cheers!

MF.

[robertp]

Unfortunately, without building new tables using an ETL tool this is the source I have to work with.

Do either of you have a good example of how one would go about pivoting the data?

[BigChris]

It's more a case of un-pivoting (if you see what I mean). You can do that by using 13 queries, one for each column, unioned together to give a single list again

[Lynn]

What type of data volumes are you dealing with? Anything you attempt on the Cognos side will likely require a lot of local processing and may not perform at all if your volumes are very large.

The only thing I can think of is to create thirteen queries, each of which returns item, location, a label to identify the time period, plus one of the metric columns. The first query would have item, location, and '2016-03' as label, and current month sales. Second query would have item, location, '2016-02', and then the prior month sales. Repeat for all the remaining sales time periods.

Union those 13 queries together. Set the aggregate for item, location, and label to none and set the metric column aggregate property to total in the union query. I would also use a coalesce function on the metric column in the union query so that any nulls turn up as zero. Such as: coalesce ( [Sales], 0 )

Again, this is in no way preferable to pivoting the data within a database table and may not work at all if your volumes are very large.

Ahhh, I see Chris has the same suggestion and beat me to the punch!

[BigChris]

Yeah, but your reply was a lot more complete than mine  :D

[Lynn]

Yeah, but your reply was a lot more complete than mine  :D

I have never been accused of being concise  :D