Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
pradeepreddy
Valued Contributor 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
jyothish8807
Honored Contributor 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
jyothish8807
Honored Contributor II

Re: Pivot Table Display

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

Best Regards,
KC
pradeepreddy
Valued Contributor 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.
jyothish8807
Honored Contributor 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
pradeepreddy
Valued Contributor 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..
jyothish8807
Honored Contributor 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
Partner
Partner

Re: Pivot Table Display

superb (y)
pradeepreddy
Valued Contributor 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.

pradeepreddy
Valued Contributor II

Re: Pivot Table Display

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