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

[#1]

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.

[#2]

can you share your what output you want

[#3]

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

[Kushal_Chawda]

try

Data:

Crosstable(Month, Data,2)

LOAD

ProjectID,

CustomerID,

Rev - Jan,

Rev - Feb,

Cost Jan,

Cost Feb,

Sales Jan,

Sales Feb

FROM Table;

Final:

noconcatenate

LOAD *,

            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;

[MarcoWedel]

Hi,

one solution could be:

table1:
CrossTable(TypeMonth, TypeMonthVal, 2)
LOAD * Inline [
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
LOAD ProjectID,
    CustomerID,
    Month(Date#(SubField(TypeMonth,' - ',2),'MMM')) as Month,
    SubField(TypeMonth,' - ',1),
    TypeMonthVal
Resident table1;

DROP Table table1;

hope this helps

regards

Marco