Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
The format of Values column is actually an array: 123;452;234
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]
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!