topic Re: Grouping in Pivot table. in QlikView

Grouping in Pivot table.

agni_gold — Tue, 05 May 2015 05:13:18 GMT

Hi friends , I am attaching the excel here , I want the result as same in excel. Can anybody held me please .

Re: Grouping in Pivot table.

tresB — Tue, 05 May 2015 05:34:21 GMT

Check the attached sample qvw

Re: Grouping in Pivot table.

Anonymous — Tue, 05 May 2015 05:34:27 GMT

Make it like Hierarchial levels if it is not present in data model; make inline loading with

Load Inline[

L1 L2

UP Kanpur

UP Lucknow

Mumbai Thane

Mumbai Andheri

]

and when u take L1 and L2 as dimensions sum(amount) as expression!! you can see desired results.

If you always want to see subtotals and totals, make it always expanded view in presentation.

Re: Grouping in Pivot table.

agni_gold — Tue, 05 May 2015 05:45:35 GMT

Can you please send the qvs file. for this .

Re: Grouping in Pivot table.

agni_gold — Tue, 05 May 2015 05:56:24 GMT

SO I have to make the table manually , is there any other way to make this table using qlikview ?

Re: Grouping in Pivot table.

Anonymous — Tue, 05 May 2015 06:05:39 GMT

Yes, you have to do it manually. How can it build cities like Kanpur,Lucknow amounts come under UP ? Somehow you should build relation as these cities are falling under UP and so on to others etc.,.

Insert --> Load Statement --> Load Inline --> desired relations.

Re: Grouping in Pivot table.

tresB — Tue, 05 May 2015 06:07:57 GMT

I guess script alone might not be very helpful here. Let me try to explain it here. You have to create an extra field that would be kind of mapping between two fields (say, city and region). It is more like praveenavelagapudi‌ suggested below. One additional aspect to highlight is - try 'Indent' mode in the presentation tab to make the two columns appear to be one playing a bit with presentation/sizing.

Re: Grouping in Pivot table.

ToniKautto — Tue, 05 May 2015 06:45:02 GMT

Of course you do not have to do it manually. You must however be able to define a logical rule that allows you to identify the blue rows, since the color information is not loaded into QlikView.

For example in the attached QVW I have assumed that you know the blue line labels, and these are defined in a in line table. Then I use this to flag the blue lines as region (guessing it could be regions ), and fill the value into the appropriate rows.

Re: Grouping in Pivot table.

ToniKautto — Tue, 05 May 2015 06:53:37 GMT

For full automation, I would consider altering the Excel file so that the blue lines are in column A, and then use this logic to identify the blue labels. See attached QVW for reference.