Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
nareshthavidishetty
Creator III
Creator III

convert columns into rows in straight or pivot table

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

EMEA1000
UK300
Site1100
Site2200
3 Replies
mdmukramali
Specialist III
Specialist III

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;

290147.PNG

nareshthavidishetty
Creator III
Creator III
Author

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
Champion III
Champion III

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.

Capture.PNG