Grouping in Pivot table.

[agni_gold]

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

[tresesco]

Check the attached sample qvw

[Anonymous]

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.

[agni_gold]

Can you please send the qvs file. for this .

[agni_gold]

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

[Anonymous]

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.

[tresesco]

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.

[ToniKautto]

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.

[ToniKautto]

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.