topic Multi Dimensional Pivot Table in App Development

Multi Dimensional Pivot Table

sakthi266 — Tue, 30 Nov 2021 16:25:32 GMT

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

Re: Multi Dimensional Pivot Table

micheledenardi — Tue, 12 Oct 2021 10:45:12 GMT

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);

Re: Multi Dimensional Pivot Table

sakthi266 — Tue, 12 Oct 2021 16:31:51 GMT

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

Re: Multi Dimensional Pivot Table

micheledenardi — Wed, 13 Oct 2021 07:20:16 GMT

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);