Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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.

4 Replies
Not applicable

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

can you share your what output you want

Not applicable

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

Hi,

Output should be like below fact table.

   

ProjectIDCustomerIDMonthRevCostSales
1001ABCJan1010010
1001ABCFeb99912
1001BCDJan111108
1001BCDFeb1080

15

Thanks,

Hom

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

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;

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

Hi,

one solution could be:

QlikCommunity_Thread_212471_Pic1.JPG

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

Community Browser