Multi Dimensional Pivot Table

[sakthi266]

Hi Everyone, 

           Good Day. I have a data as mentioned in Excel sheet attached(Data). Also i want to show like mentioned in the Second Sheet(Requirements). Will you please reply fast as there was a immediate requirement. 

Thanks and Regards, 

Sakthivel.S

[micheledenardi]

Check this:

MapTypeCategoty:
Mapping
Load * Inline [
Type,Category
I-1,Interest
I-2,Interest
D-1,Dividends
D-2,Dividends
ST,Gain/Loses
LT,Gain/Loses
Oth,Others];

MapTypeDesc:
Mapping
Load * inline [
Type,Desc
I-1,Interest-1
I-2,Interest-2
D-1,Dividends-1
D-2,Dividends-2
ST,Short-Term
LT,Long-Term
Oth,Others];

LOAD
    Entity,
    "Account Name",
    ApplyMap('MapTypeCategoty',Type,'Not Set')as TypeCategory,
    ApplyMap('MapTypeDesc',Type,'Not Set') as TypeDesc,
    Alt(Amount1,0)+Alt(Amount2,0)+Alt(Amount3,0)+Alt(Amount4,0) As Amount
FROM [lib://BaseFolder/3.DEV/_Temp/Data.xlsx]
(ooxml, embedded labels, table is Data);

[sakthi266]

Hi Micheledenardi, 

                     Sorry i am not getting the solution which i want due to the data which i am having is other way around. Attached for the reference. Earlier which i shared the data is wrong

Thanks in advance, 

Sakthivel.S

[micheledenardi · Accepted solution]

Try this:

MapTypeDesc:
Mapping
Load * inline [
Type,Desc
I|I-1,Interest-1
I|I-2,Interest-2
I|Others,Other Interest
D|D-1,Dividends-1
D|D-2,Dividends-2
D|Others,Other Divident
ST,Short-Term
LT,Long-Term
Others,Others];

Final:
LOAD
    Entity,
    "Account Name",
    "Type",
    'Interest' as TypeCategory,
    ApplyMap('MapTypeDesc','I|'&Type,'Not Set') as TypeDesc,
    "Date",
    Interest as Value
FROM [lib://BaseFolder/3.DEV/_Temp/Data2.xlsx]
(ooxml, embedded labels, table is Data)
	Where isnum(Interest);

Concatenate(Final)
LOAD
    Entity,
    "Account Name",
    "Type",
    'Dividends' as TypeCategory,
    "Date",
    ApplyMap('MapTypeDesc','D|'&Type,'Not Set') as TypeDesc,
    Dividend as Value
FROM [lib://BaseFolder/3.DEV/_Temp/Data2.xlsx]
(ooxml, embedded labels, table is Data)
	Where isnum(Dividend);

Concatenate(Final)
LOAD
    Entity,
    "Account Name",
    "Type",
    'Gain/Loses' as TypeCategory,
    "Date",
    ApplyMap('MapTypeDesc',Type,'Not Set') as TypeDesc,
    GL 	as Value
FROM [lib://BaseFolder/3.DEV/_Temp/Data2.xlsx]
(ooxml, embedded labels, table is Data)
	Where isnum(GL);

Concatenate(Final)
LOAD
    Entity,
    "Account Name",
    "Type",
    'Others' as TypeCategory,
    "Date",
    'Others' as TypeDesc,
    "Other 1"+"Other 2"+Other as Value
FROM [lib://BaseFolder/3.DEV/_Temp/Data2.xlsx]
(ooxml, embedded labels, table is Data)
	Where isnum("Other 1") or isnum("Other 2") or isnum(Other);