If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Unpivot / Pivot

Started by dougp, 02 Mar 2022 02:25:03 PM

Previous topic - Next topic

dougp

I need to manipulate data in a data module.  The data I have is in multiple tabs in Excel.  All of the tabs have the same number of columns with the same names.  But the data is not quite what I want.  It was simple enough to UNION these in a data module.  But my measure is split across 24 (hourly) columns.


ScaleDateWeek01234567891011121314151617181920212223Total
71/1/2018Mon22613571612262221242727171625131616152312354
71/2/2018Tue2515212840507991132147179144280341263235114353519332321342,384
71/3/2018Wed313336324560187951591451741591671201179363394524303729231,943
71/4/2018Thu26434062516613626814515929744228127230326040343427442237273,116
71/5/2018Fri21303840487117914114148446446747541332311661353022212327203,690

I need to UNPIVOT the measures (and delete some columns).  My ideal end product would look like this:


ScaleDateHourVolume
71/1/201802
71/1/201812
71/1/201826
71/1/201831
71/1/201843
71/1/201855
71/1/201867
71/1/2018716
71/1/2018812

Power Query (so, Excel and Power BI) can do this.

I found this idea on IBM's RFE site.  It was created 2 years ago and looks like it's listed as "being considered".

Is there really no way to UNPIVOT data in a data module?
Do I need to use Power Query to do this in Excel before uploading the file into Cognos?

MFGF

Quote from: dougp on 02 Mar 2022 02:25:03 PM
I need to manipulate data in a data module.  The data I have is in multiple tabs in Excel.  All of the tabs have the same number of columns with the same names.  But the data is not quite what I want.  It was simple enough to UNION these in a data module.  But my measure is split across 24 (hourly) columns.


ScaleDateWeek01234567891011121314151617181920212223Total
71/1/2018Mon22613571612262221242727171625131616152312354
71/2/2018Tue2515212840507991132147179144280341263235114353519332321342,384
71/3/2018Wed313336324560187951591451741591671201179363394524303729231,943
71/4/2018Thu26434062516613626814515929744228127230326040343427442237273,116
71/5/2018Fri21303840487117914114148446446747541332311661353022212327203,690

I need to UNPIVOT the measures (and delete some columns).  My ideal end product would look like this:


ScaleDateHourVolume
71/1/201802
71/1/201812
71/1/201826
71/1/201831
71/1/201843
71/1/201855
71/1/201867
71/1/2018716
71/1/2018812

Power Query (so, Excel and Power BI) can do this.

I found this idea on IBM's RFE site.  It was created 2 years ago and looks like it's listed as "being considered".

Is there really no way to UNPIVOT data in a data module?
Do I need to use Power Query to do this in Excel before uploading the file into Cognos?

Hi Doug,

IBM used to have a great tool for this - Cognos Data Manager - but it was deprecated a few years ago. There isn't anything in-the-box with Cognos Analytics that can solve this easily. If you happen to be using CA within IBM Cloud Pak for Data, I think the Data Refinery piece of ICP4D can do this, but obviously this isn't there for an on-prem or CA on Cloud instance.

Your best bet is to look at your ETL tool I'd say. If you want a point solution, something like Alteryx Designer or Excel would suffice too, so PQ in Excel sounds like your best bet.

Cheers!

MF.
Meep!