topic Re: header group by in QlikView

header group by

capriconuser — Wed, 25 Nov 2020 16:16:04 GMT

i have a data like this

MAIN	SUB	Mode	Sum
		Single	123
		Multiple	123
		Double	123
	A		369
		Single	345
		Multiple	234
		Double	12
	B		591
		Single	575
		Multiple	576
		Double	577
	c		1728
		Single	579
		Multiple	580
		Double	581
	d		1740
		Single	583
		Multiple	584
		Double	585
	e		1752
		Single	587
		Multiple	588
		Double	589
	f		1764
		Single	591
		Multiple	592
		Double	593
	g		1776
abc			19440
		Single	596
		Multiple	597
		Double	598
	A		1791
		Single	600
		Multiple	601
		Double	602
	B		1803
		Single	604
		Multiple	605
		Double	606
	C		1815
		Single	608
		Multiple	609
		Double	610
	D		1827
		Single	612
		Multiple	613
		Double	614
	E		1839
		Single	616
		Multiple	617
		Double	618
	F		1851
		Single	620
		Multiple	621
		Double	622
	G		1863
abc1			25578

i want to group by mode column as header and split every mode like this

this is for single want same for multiple and double in same table

MAIN	SINGLE
A	123
B	345
C	575
D	579
E	583
F	587
G	591
ABC	3383

A	596
B	600
C	604
D	608
E	612
F	616
G	620
ABC1	4256

how i do this

Re: header group by

thevingo — Thu, 22 Mar 2018 09:00:52 GMT

first you can combine MAIN & SUB by concating(&-operator) both fieds into 1 or you can use if..else as If(MAIN='', SUB, MAIN) as Combine.

Then just create a new resident table as

Load Combine,

SUM as SINGLE

Resident Above_Table

where Mode='Single'.

Additionally, if you wants to combine all Modes into one, do above steps for Mode='Double' & 'Multiple' with the same field-names. This way they will auto-concate into one

Re: header group by

capriconuser — Thu, 22 Mar 2018 09:51:43 GMT

using concatenate this shows like this

ABC A

ABC B

ABC D

where as i want ABC at the end like i mentioned in question

and will you please more explain second point

I ATTACHED FILE PLEASE CHECK

Re: header group by

capriconuser — Thu, 22 Mar 2018 10:05:47 GMT

and what is combine here?

Re: header group by

thevingo — Thu, 22 Mar 2018 10:16:32 GMT

Sorry, I have Qlik Personal edition, so I can't see your application.

My second point is that you can create 3 separate tables for Modes(Single, Double & Multi) by using where

Table1:

Load MAIN,

SUB,

Sum(Value) as Value

Resident Above_Table

Group by MAIN, SUB

where Mode='Single';

Load MAIN,

SUB,

Sum(Value) as Value

Resident Above_Table

Group by MAIN, SUB

where Mode='Double';

Load MAIN,

SUB,

Sum(Value) as Value

Resident Above_Table

Group by MAIN, SUB

where Mode='Multiple';

As all the tables have same fields they will auto-concate or append into 1 single table as Table1

Re: header group by

thevingo — Thu, 22 Mar 2018 10:22:34 GMT

Combine is field calculated as MAIN&' '&SUB from the script.

Also you can use Crosstab while exporting/loading data through data transformation step

Re: header group by

capriconuser — Thu, 22 Mar 2018 10:22:37 GMT

above_table means sheet name ?

FROM

[Lsheets1.xlsx]

(ooxml, embedded labels, table is Lif);

or Lsheet1?

Re: header group by

capriconuser — Thu, 22 Mar 2018 10:30:30 GMT

i do this

Directory;

LOAD MAIN,

SUB,

MODE,

VALUES

FROM

Book2.xlsx

(ooxml, embedded labels, table is Sheet1);

Table1:

Load MAIN,

SUB,

Sum(VALUES) as VALUES

Resident Sheet1

where MODE='Single'

Group by MAIN, SUB

but this is not working

Re: header group by

capriconuser — Thu, 22 Mar 2018 10:34:32 GMT

now i just want "mode" column as header

Re: header group by

sunny_talwar — Thu, 22 Mar 2018 11:05:25 GMT

Like this?

Just drag where it says MODE to the top of the chart and it should be pivoted....

Re: header group by

capriconuser — Thu, 22 Mar 2018 11:15:53 GMT

nops

l want every mode with separate table but in 1 table

like this and same for multiple

multiple comes after single and the double comes after multiple

Re: header group by

sunny_talwar — Thu, 22 Mar 2018 11:17:50 GMT

So you want three charts?

Re: header group by

capriconuser — Thu, 22 Mar 2018 11:22:50 GMT

yes but in 1 form

Re: header group by

sunny_talwar — Thu, 22 Mar 2018 11:23:51 GMT

What do you mean when you say 1 form? Can you elaborate?

Re: header group by

sunny_talwar — Thu, 22 Mar 2018 11:27:31 GMT

Is this what you are trying to make?

group by sum

Re: header group by

capriconuser — Thu, 22 Mar 2018 11:28:36 GMT

yes exactly.. this is also my question 😕

Re: header group by

sunny_talwar — Thu, 22 Mar 2018 11:35:19 GMT

This is probably the best you would be able to do...

Re: header group by

thevingo — Thu, 22 Mar 2018 12:06:20 GMT

Is this fine

For making groups, I've added Key column from the script and on Presentation tab check 'Show partial sum' for MAIN