Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
capriconuser
Creator
Creator

header group by

i have a data like this

  

MAINSUBModeSum
Single123
Multiple123
Double123
A369
Single345
Multiple234
Double12
B591
Single575
Multiple576
Double577
c1728
Single579
Multiple580
Double581
d1740
Single583
Multiple584
Double585
e1752
Single587
Multiple588
Double589
f1764
Single591
Multiple592
Double593
g1776
abc19440
Single596
Multiple597
Double598
A1791
Single600
Multiple601
Double602
B1803
Single604
Multiple605
Double606
C1815
Single608
Multiple609
Double610
D1827
Single612
Multiple613
Double614
E1839
Single616
Multiple617
Double618
F1851
Single620
Multiple621
Double622
G1863
abc125578

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

MAINSINGLE
A123
B345
C575
D579
E583
F587
G591
ABC3383
A596
B600
C604
D608
E612
F616
G620
ABC14256

how i do this

17 Replies
thevingo
Creator
Creator

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
Creator
Creator
Author

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
Creator
Creator
Author

and what is combine here?

thevingo
Creator
Creator

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

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

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

Captureaa.PNG

capriconuser
Creator
Creator
Author

above_table means sheet name ?

FROM

[Lsheets1.xlsx]

(ooxml, embedded labels, table is Lif);

or Lsheet1?

capriconuser
Creator
Creator
Author

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

Capture.PNG

capriconuser
Creator
Creator
Author

now i just want "mode" column as header

sunny_talwar

Like this?

Capture.PNG

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