topic Re: Unpivot last 2 columns in App Development

Unpivot last 2 columns

Mazilinho — Tue, 31 May 2022 11:30:03 GMT

Hello, I have the following scenario:

Category	Brand	KPI	Values
Brand	Sony	A	123;322;221
Brand	Sony	B	234;221;232

I need the data to be displayed like this:

Category	Brand	KPI A Value	KPI B Value
Brand	Sony	123;322;221	234;221;232

How can I obtain this? I tried to use IF clause to calculate 2 new columns and then use only() in the load script to obtain that format, but it results no rows when I use only().

Thanks!

Re: Unpivot last 2 columns

Or — Tue, 31 May 2022 11:10:06 GMT

Load Category, Brand, sum(if(KPI = 'A',Values)) as [KPI A Value], sum(if(KPI = 'B',Values)) as [KPI B Value]

From YourTable

Group by Category, Brand;

Sample:

YourTable:
Load * INLINE [
Category, Brand, KPI, Values
Brand, Sony, A, 123
Brand, Sony, B, 234 ];

Load Category, Brand, sum(if(KPI = 'A',Values)) as [KPI A Value], sum(if(KPI = 'B',Values)) as [KPI B Value]
Resident YourTable
Group by Category, Brand;

Drop Table YourTable;

Re: Unpivot last 2 columns

Mazilinho — Tue, 31 May 2022 11:22:03 GMT

The format of Values column is actually an array: 123;452;234

Re: Unpivot last 2 columns

Or — Tue, 31 May 2022 12:40:53 GMT

Didn't catch that. Just replace the sum() with only(), then.

Load Category, Brand, Only(if(KPI = 'A',Values)) as [KPI A Value], Only(if(KPI = 'B',Values)) as [KPI B Value]

Re: Unpivot last 2 columns

Mazilinho — Tue, 31 May 2022 12:48:34 GMT

Thanks, yes, I fixed it with only() in the end. Not sure what I was doing wrong, but the app was crashing when I used only(). Thanks for help!