convert columns into rows in straight or pivot table

[nareshthavidishetty]

Hello all,

Table:

LOAD * INLINE [

Region, Country, Site, Value

    EMEA, UK, UK_Site1,100

    EMEA, UK, UK_Site2,200

    EMEA, Finland, Finland_Site1,300

    EMEA, Finland, Finland_Site2,400

    NA, Canada, Canada_Site1,100

    NA, Canada, Canada_Site2,500

    NA, USA, USA_Site2,100

    NA, USA, USA_Site4,600

    NA, USA, USA_Site3,700

    NA, USA, USA_Site1,800

];

I have a data like above Inline table and my requirement is

based on the selections I want to show the associated sites  along with selected Region and Country like below table[Region='EMEA',Country='USA'  and no selection on Sites]

Temp_Dim	Value
EMEA	1000
UK	300
Site1	100
Site2	200

[mdmukramali]

Hi,

Maybe like this.

Table:
LOAD * INLINE [
Region, Country, Site, Value
    EMEA, UK, UK_Site1,100
    EMEA, UK, UK_Site2,200
    EMEA, Finland, Finland_Site1,300
    EMEA, Finland, Finland_Site2,400
    NA, Canada, Canada_Site1,100
    NA, Canada, Canada_Site2,500
    NA, USA, USA_Site2,100
    NA, USA, USA_Site4,600
    NA, USA, USA_Site3,70
    NA, USA, USA_Site1,800
];
Temp_Data:
LOAD
Region as Temp_Dim,
Sum(Value) as Values
Resident Table
Group by Region;
LOAD
Country as Temp_Dim,
Sum(Value) as Values
Resident Table
Group by Country;
LOAD
Site as Temp_Dim,
Sum(Value) as Values
Resident Table
Group by Site;
DROP Table Table;

[nareshthavidishetty]

Hi Ali,

Thanks for your quick reply..

I want to make a selections on the filters,if we use the above code we cannot  make a selections for country and Region.

Can we acheive these in the front end? I have tried  value list but no luck.

Please find the  sample application attached in the discussion for your information.

THanks,

Bhargav

[vishsaggi]

Try this:

Region:

LOAD * INLINE [ 

Region, Country, Site, Value 

    EMEA, UK, UK_Site1,100 

    EMEA, UK, UK_Site2,200 

    EMEA, Finland, Finland_Site1,300 

    EMEA, Finland, Finland_Site2,400 

    NA, Canada, Canada_Site1,100 

    NA, Canada, Canada_Site2,500 

    NA, USA, USA_Site2,100 

    NA, USA, USA_Site4,600 

    NA, USA, USA_Site3,70 

    NA, USA, USA_Site1,800 

]; 

Dim:

LOAD * INLINE [

Dim

1

2

3

];

Then using Straight table or Pivot table write the below dimension and expressions.

Thanks to stalwar1 for this trick i learnt from.

Dim: = Pick(Dim, Region, Country, Site)

Expr: = Pick(Dim, Sum({1} Value), Sum(Total <Country> Value), Sum(Value))

Then add your fields as filters and select accordingly.