Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

How to make a Pivot Table like in this example?

Please help me to make a Pivot Table like this one:

Capture.PNG

I believe it is a Pivot Table with groups. I've been reading in the community of any ideas how to make one like this but I can't find clear instructions.

I only have one dimension and one expression:

AREASales
GMM East6249006
GMM North8756996
GMM South10315633
GMM West1415876

Thanks for the help!

1 Solution

Accepted Solutions
Highlighted

Like this?

Capture.PNG

Script used:

Table:

LOAD *,

  SubField(AREA, ' ', 1) as Field1,

  SubField(AREA, ' ', 2) as Field3,

  If(Match(SubField(AREA, ' ', 2), 'South', 'North'), 'Section 1', 'Section 2') as Field2

INLINE [

    AREA, Sales

    GMM East, 6249006

    GMM North, 8756996

    GMM South, 10315633

    GMM West, 1415876

];

View solution in original post

4 Replies
Highlighted
Creator II
Creator II

Depending on your data model, you could concatenate your dimension to be

GMM fieldname & ' ' &Direction fieldname

or create an expression for each

1.Sum({$<Fieldname1 ={'GMM'}, Fieldname2 ={'East'}>}Sales)

2. Sum({$<Fieldname1 ={'GMM'}, Fieldname2 ={'North'}>}Sales)

3. Sum({$<Fieldname1 ={'GMM'}, Fieldname2 ={'South'}>}Sales)

4. Sum({$<Fieldname1 ={'GMM'}, Fieldname2 ={'East'}>}Sales)

And have the expression name as GMM East

Highlighted

Like this?

Capture.PNG

Script used:

Table:

LOAD *,

  SubField(AREA, ' ', 1) as Field1,

  SubField(AREA, ' ', 2) as Field3,

  If(Match(SubField(AREA, ' ', 2), 'South', 'North'), 'Section 1', 'Section 2') as Field2

INLINE [

    AREA, Sales

    GMM East, 6249006

    GMM North, 8756996

    GMM South, 10315633

    GMM West, 1415876

];

View solution in original post

Highlighted
Partner
Partner

HI.

You would have to make the structure in Script.

Something Like Load

Data:

Load

     Sales

     LEFT(AREA,3) as GMM

     SUBFIELD(AREA,' ' ,2) as Area

     IF(SUBFIELD(AREA,' ' ,2)  = 'South' OR SUBFIELD(AREA,' ' ,2)  = 'North',

          'Section 1',

          'Section 2') as Section;

From YourData.


You then just add the individual fields to your Pivot.


Mark

Highlighted
Not applicable

Hi,

You can create the required 3 fields from "AREA" in the script itself..