topic Re: convert columns into rows in straight or pivot table in QlikView

convert columns into rows in straight or pivot table

nareshthavidishetty — Sat, 03 Feb 2018 14:38:12 GMT

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

Re: convert columns into rows in straight or pivot table

mdmukramali — Sat, 03 Feb 2018 15:33:39 GMT

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;

Re: convert columns into rows in straight or pivot table

nareshthavidishetty — Sat, 03 Feb 2018 16:49:34 GMT

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

Re: convert columns into rows in straight or pivot table

vishsaggi — Sun, 04 Feb 2018 02:01:57 GMT

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

];

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.