Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
can you share your what output you want
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
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;
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