COGNOiSe.com - The IBM Cognos Community

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.

Scale	Date	Week	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	Total
7	1/1/2018	Mon	2	2	6	1	3	5	7	16	12	26	22	21	24	27	27	17	16	25	13	16	16	15	23	12	354
7	1/2/2018	Tue	25	15	21	28	40	50	79	91	132	147	179	144	280	341	263	235	114	35	35	19	33	23	21	34	2,384
7	1/3/2018	Wed	31	33	36	32	45	60	187	95	159	145	174	159	167	120	117	93	63	39	45	24	30	37	29	23	1,943
7	1/4/2018	Thu	26	43	40	62	51	66	136	268	145	159	297	442	281	272	303	260	40	34	34	27	44	22	37	27	3,116
7	1/5/2018	Fri	21	30	38	40	48	71	179	141	141	484	464	467	475	413	323	116	61	35	30	22	21	23	27	20	3,690

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

Scale	Date	Hour	Volume
7	1/1/2018	0	2
7	1/1/2018	1	2
7	1/1/2018	2	6
7	1/1/2018	3	1
7	1/1/2018	4	3
7	1/1/2018	5	5
7	1/1/2018	6	7
7	1/1/2018	7	16
7	1/1/2018	8	12

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

I found this idea (https://ibm-data-and-ai.ideas.ibm.com/ideas/CA-I-2619) 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?

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.

Scale	Date	Week	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	Total
7	1/1/2018	Mon	2	2	6	1	3	5	7	16	12	26	22	21	24	27	27	17	16	25	13	16	16	15	23	12	354
7	1/2/2018	Tue	25	15	21	28	40	50	79	91	132	147	179	144	280	341	263	235	114	35	35	19	33	23	21	34	2,384
7	1/3/2018	Wed	31	33	36	32	45	60	187	95	159	145	174	159	167	120	117	93	63	39	45	24	30	37	29	23	1,943
7	1/4/2018	Thu	26	43	40	62	51	66	136	268	145	159	297	442	281	272	303	260	40	34	34	27	44	22	37	27	3,116
7	1/5/2018	Fri	21	30	38	40	48	71	179	141	141	484	464	467	475	413	323	116	61	35	30	22	21	23	27	20	3,690

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

Scale	Date	Hour	Volume
7	1/1/2018	0	2
7	1/1/2018	1	2
7	1/1/2018	2	6
7	1/1/2018	3	1
7	1/1/2018	4	3
7	1/1/2018	5	5
7	1/1/2018	6	7
7	1/1/2018	7	16
7	1/1/2018	8	12

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

I found this idea (https://ibm-data-and-ai.ideas.ibm.com/ideas/CA-I-2619) 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.