topic Re: Pivot Table Display in QlikView

Pivot Table Display

PradeepReddy — Sat, 16 Nov 2024 05:00:11 GMT

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

Re: Pivot Table Display

jyothish8807 — Tue, 11 Dec 2018 06:08:58 GMT

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;

Re: Pivot Table Display

jyothish8807 — Tue, 11 Dec 2018 06:09:49 GMT

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

Re: Pivot Table Display

PradeepReddy — Tue, 11 Dec 2018 06:33:02 GMT

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.

Re: Pivot Table Display

jyothish8807 — Tue, 11 Dec 2018 06:38:43 GMT

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 🙂

Re: Pivot Table Display

PradeepReddy — Tue, 11 Dec 2018 06:53:43 GMT

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..

Re: Pivot Table Display

jyothish8807 — Tue, 11 Dec 2018 06:58:07 GMT

Hi Pradeep,

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

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

~~Left join~~
Load
ID,
CustID as NewCustID
resident A;

Re: Pivot Table Display

shreya_nadkarni — Tue, 11 Dec 2018 07:15:32 GMT

superb (y)

Re: Pivot Table Display

PradeepReddy — Tue, 11 Dec 2018 07:46:12 GMT

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.

Re: Pivot Table Display

PradeepReddy — Tue, 11 Dec 2018 07:48:58 GMT

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

Re: Pivot Table Display

shreya_nadkarni — Tue, 11 Dec 2018 07:55:36 GMT

Hi,

You can try this script.

B:
Load
distinct
Parent_CustID,
CustID as new_id
resident A;

Thanks,

Shreya

Re: Pivot Table Display

PradeepReddy — Tue, 11 Dec 2018 08:19:12 GMT

1st pivot showing correct info..
2nd pivot (having only 'Cust2') not showing correct numbers, it should show 11 and 21 respectively.
2nd table you can consider as simple table for simplicity purpose.

appreciate your efforts.

Re: Pivot Table Display

shreya_nadkarni — Tue, 11 Dec 2018 09:00:23 GMT

Hi,

If there is single selection for New Id

Then the detail info will show the output(Attached qvw).

Expression modified.

Thanks.