header group by

[capriconuser]

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

[thevingo]

Hi

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

[capriconuser]

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

[capriconuser]

and what is combine here?

[thevingo]

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

[thevingo]

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

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

[capriconuser]

above_table means sheet name ?

FROM

[Lsheets1.xlsx]

(ooxml, embedded labels, table is Lif);

or Lsheet1?

[capriconuser]

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

[capriconuser]

now i just want "mode" column as header

[sunny_talwar]

Like this?

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