4 Replies Latest reply: Apr 10, 2016 5:04 PM by Marco Wedel

# How to covert column wise fact table to Row waise fact table?

Hi,

I have a fact table which is a column wise fact table (listed below), how to convert into row wise fact table?

 ProjectID, CustomerID, Rev - Jan, Rev - Feb, Cost Jan, Cost Feb, Sales Jan, Sales Feb

Thanks,

Hom.

• ###### Re: How to covert column wise fact table to Row waise fact table?

can you share your what output you want

• ###### Re: How to covert column wise fact table to Row waise fact table?

Hi,

Output should be like below fact table.

 ProjectID CustomerID Month Rev Cost Sales 1001 ABC Jan 10 100 10 1001 ABC Feb 9 99 12 1001 BCD Jan 11 110 8 1001 BCD Feb 10 80 15

Thanks,

Hom

• ###### Re: How to covert column wise fact table to Row waise fact table?

Hi,

one solution could be:

```table1:
CrossTable(TypeMonth, TypeMonthVal, 2)
ProjectID, CustomerID, Rev - Jan, Rev - Feb, Cost - Jan, Cost - Feb, Sales - Jan, Sales - Feb
1001, ABC, 10, 9, 100, 99, 10, 12
1001, BCD, 11, 10, 110, 80, 8, 15
];

table2:
Generic
CustomerID,
Month(Date#(SubField(TypeMonth,' - ',2),'MMM')) as Month,
SubField(TypeMonth,' - ',1),
TypeMonthVal
Resident table1;

DROP Table table1;
```

hope this helps

regards

Marco

• ###### Re: How to covert column wise fact table to Row waise fact table?

try

Data:

Crosstable(Month, Data,2)

 ProjectID, CustomerID, Rev - Jan, Rev - Feb, Cost Jan, Cost Feb, Sales Jan, Sales Feb

FROM Table;

Final:

noconcatenate

if(wildmatch(Month,'Rev*'),Data) as Revenue,

if(wildmatch(Month,'Sales*'),Data) as Sales,

if(wildmatch(Month,'Cost*'),Data) as Cost,

month(date#(subfield(Month,' ',2),'MMM'))  as MONTH

Resident Data;

drop table Data;