Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

Re: How to make a Pivot Table like in this example?

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

];

4 Replies
squeakie_pig
Contributor

Re: How to make a Pivot Table like in this example?

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

Re: How to make a Pivot Table like in this example?

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

];

Partner
Partner

Re: How to make a Pivot Table like in this example?

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

Not applicable

Re: How to make a Pivot Table like in this example?

Hi,

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