Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist II
Specialist II

Pivot Table Display

Hi all,

Good day..

My data looks like below.

CustID,Parent_CustID,Measure1,Measure2,Country
Cust1, Cust1, 10, 20, Country1
Cust2, Cust1, 11, 21, Country2
Cust3, Cust1, 12, 22, Country2
Cust4, Cust4, 13, 23, Country3
Cust5, Cust6, 14, 24, Country4
Cust6, Cust6, 15, 25, Country4
Cust7, Cust7, 16, 26, Country4
Cust8, Cust7, 17, 27, Country5
Cust9, Cust9, 18, 28, Country6

Requirement:

I have a list box with CustID for selections. 

I have pivot table with dimensions.. Parent_CustID & CustID in same order; Expressions: Sum(Measure1)... 10 measures.

When I select any value from List box(CustID), the pivot table should showcase all the customers with in the group(Parent_CustID).

Expected Output:

1) When I select Cust2 in the list Box, the pivot table has to show below info.

CustID,Parent_CustID,Measure1,Measure2
Cust1, Cust1, 10, 20
Cust2, Cust1, 11, 21
Cust3, Cust1, 12, 22

2) When I select Cust4 in the list Box, the pivot table has to show below info.

CustID,Parent_CustID,Measure1,Measure2
Cust4, Cust4, 13, 23

as there are lot of expressions/measures, I am trying to restrict the data at dimension level.

Edited:  Dashboard contains a lot of filters and other charts. The above stated scenario applies to only one pivot chart. In rest of the charts/filters, should show normally i.e. when I select Cust2 , it should reflect Cust2 related data only. To illustrate this I have added one more dimension Country to the data sample.

Thanks in advance

Labels (3)
12 Replies
Highlighted
Master II
Master II

Re: Pivot Table Display

Hi Pradeep,

Try this:

A:
LOAD * Inline [
CustID,Parent_CustID,Measure1,Measure2
Cust1, Cust1, 10, 20
Cust2, Cust1, 11, 21
Cust3, Cust1, 12, 22
Cust4, Cust4, 13, 23
Cust5, Cust6, 14, 24
Cust6, Cust6, 15, 25
Cust7, Cust7, 16, 26
Cust8, Cust7, 17, 27
Cust9, Cust9, 18, 28
];

Left join
B:
Load
distinct
Parent_CustID,
AutoNumber(Parent_CustID) as ID
resident A;

Left join
Load
ID,
CustID as NewCustID
resident A;

Best Regards,
KC
Highlighted
Master II
Master II

Re: Pivot Table Display

Just use 'NewCustID' in the list box and 'CustID' in pivot as dimension 🙂

Best Regards,
KC
Highlighted
Specialist II
Specialist II

Re: Pivot Table Display

thanks jyothish for quick reply.
I have given sample data and this is small part of data model.
Actual table contains around 1million records and each group contains on average 15-20 customers. If we do the join it will impact the size of the app and RAM usage.

So trying achieve with existing table structure as given, with slight modifications.
Highlighted
Master II
Master II

Re: Pivot Table Display

Hi Pradeep,

From this logic we are creating only one new field 'NewCustID' also since the values are same as CustID it will not increase the size of the app much.

It will increase the load time for sure 🙂

Best Regards,
KC
Highlighted
Specialist II
Specialist II

Re: Pivot Table Display

Its correct, the reload time will increase and a small amount of application size.
But if we did not select any NewCustID(from your solution) the measures will show wrong numbers. To avoid this we have to change the simple expressions to little complex expressions(10 expressions), which in turn leads to performance, on each other selections
And also as per your suggested process, we have to choose 'always one NewCustID' in order to show the correct numbers. Which is not the scenario..

sincerely appreciate your efforts..
Highlighted
Master II
Master II

Re: Pivot Table Display

Hi Pradeep,

Just removed the left join, now the sum will be correct 🙂

A:
LOAD * Inline [
CustID,Parent_CustID,Measure1,Measure2
Cust1, Cust1, 10, 20
Cust2, Cust1, 11, 21
Cust3, Cust1, 12, 22
Cust4, Cust4, 13, 23
Cust5, Cust6, 14, 24
Cust6, Cust6, 15, 25
Cust7, Cust7, 16, 26
Cust8, Cust7, 17, 27
Cust9, Cust9, 18, 28
];

Left join
B:
Load
distinct
Parent_CustID,
AutoNumber(Parent_CustID) as ID
resident A;

Left join
Load
ID,
CustID as NewCustID
resident A;

Best Regards,
KC
Highlighted
Partner
Partner

Re: Pivot Table Display

superb (y)
Highlighted
Specialist II
Specialist II

Re: Pivot Table Display

if we take other filter/chart, when we select the data from NewCustID, it will give you wrong results.

Might be I missed to mention in my earlier post, there are other filters/ charts in the dashboard.

to illustrate the same, I have added one Country field, please see the attachment.

Highlighted
Specialist II
Specialist II

Re: Pivot Table Display

I have to use one list box only to select the customer, either NewCustomerID or Cust_ID