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: 
luigi_b
Partner - Contributor
Partner - Contributor

Group a straight table

Hello,

I have an issue representing my data in a table. My data is basically structured like this:

Dimension1Dimension2Dimension3Description (text)
P1--Desc P1
P1G1-Desc G1
P1G1K1Desc K1
P2--Desc P2
P2G2-Desc G2
P2G2K2Desc K2
P2G2K3Desc K3
P2G3-Desc G3
P2G3K4Desc K4
P3--Desc P3
P3G4-Desc G4
P3G4K5Desc K5

 

What I want to obtain is kind of a straight table where each dimension is grouped, in the same way data is shown in a pivot table. It should look like this, possibly with icons to expand/contract groups:

DimensionDescription (text)

P1

       G1

              K1

Desc P1

Desc G1

Desc K1

P2

     G2

           K2

           K3

    G3

          K4

Desc P2

Desc G2

Desc K2

Desc K3

Desc G3

Desc K4

P3

      G4

             K5

Desc P3

Desc G4

Desc K5

 

I have been trying to use a pivot table, but wasn't successful because each dimension has its own values. Thus values are shown only for lower dimensions (K1,K2 etc.), while for upper dimensions (P,G) nothing is shown since they have multiple values associated. I can't split the measure Description in multiple columns, due to requirements for this project.

Is there a way to obtain a table as the one shown above?

Labels (4)
1 Solution

Accepted Solutions
ArnadoSandoval
Specialist II
Specialist II

Hi @luigi_b 

You can prepare the Dimension field inside your loading script, these are the concept you apply while doing it

This is the script implementing the solution

Let vNoBreakSpace = chr(160)&chr(160)&chr(160)&chr(160)&chr(160)&chr(160)&chr(160);
NoConcatenate

GroupTable:
Load RowNo() As Xseq, * Inline [
Dimension1,Dimension2,Dimension3,Description (text)
P1,,,Desc P1
P1, G1,,Desc G1
P1, G1, K1,Desc K1
P2,,,Desc P2
P2, G2,,Desc G2
P2, G2, K2,Desc K2
P2, G2, K3,Desc K3
P2, G3,,Desc G3
P2, G3, K4,Desc K4
P3,,,Desc P3
P3, G4,,Desc G4
P3, G4,K5,Desc K5
];

NoConcatenate 

NewTable:
Load nSeq,
     Dimension,
     Description
;
Load *,
     nDim1 & nDim2 & nDim3 As Dimension
;
Load Xseq as nSeq, 
     If(IsNull(Previous(Dimension1)), Dimension1, If(Previous(Dimension1) <> Dimension1, Dimension1, '$(vNoBreakSpace)' )) As nDim1,
     If(IsNull(Previous(Dimension2)), Dimension2, If(Previous(Dimension2) <> Dimension2, Dimension2, '$(vNoBreakSpace)' )) As nDim2,
     If(IsNull(Previous(Dimension3)), Dimension3, If(Previous(Dimension3) <> Dimension3, Dimension3, '$(vNoBreakSpace)' )) As nDim3,
     [Description (text)] As Description

Resident GroupTable;
  • We introduced a Sequence field, Xseq and nSeq without it, the straight table does its own sorting, and by using straight tables we do not have control to suppress this required extra column.
  • The variable vNoBreakSpace was used to handle 5 non-breakable spaces avoiding extra typing.

The resulting straight table looks like this:

Group_Straight_Table_01.jpg

Hope this helps,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.

View solution in original post

1 Reply
ArnadoSandoval
Specialist II
Specialist II

Hi @luigi_b 

You can prepare the Dimension field inside your loading script, these are the concept you apply while doing it

This is the script implementing the solution

Let vNoBreakSpace = chr(160)&chr(160)&chr(160)&chr(160)&chr(160)&chr(160)&chr(160);
NoConcatenate

GroupTable:
Load RowNo() As Xseq, * Inline [
Dimension1,Dimension2,Dimension3,Description (text)
P1,,,Desc P1
P1, G1,,Desc G1
P1, G1, K1,Desc K1
P2,,,Desc P2
P2, G2,,Desc G2
P2, G2, K2,Desc K2
P2, G2, K3,Desc K3
P2, G3,,Desc G3
P2, G3, K4,Desc K4
P3,,,Desc P3
P3, G4,,Desc G4
P3, G4,K5,Desc K5
];

NoConcatenate 

NewTable:
Load nSeq,
     Dimension,
     Description
;
Load *,
     nDim1 & nDim2 & nDim3 As Dimension
;
Load Xseq as nSeq, 
     If(IsNull(Previous(Dimension1)), Dimension1, If(Previous(Dimension1) <> Dimension1, Dimension1, '$(vNoBreakSpace)' )) As nDim1,
     If(IsNull(Previous(Dimension2)), Dimension2, If(Previous(Dimension2) <> Dimension2, Dimension2, '$(vNoBreakSpace)' )) As nDim2,
     If(IsNull(Previous(Dimension3)), Dimension3, If(Previous(Dimension3) <> Dimension3, Dimension3, '$(vNoBreakSpace)' )) As nDim3,
     [Description (text)] As Description

Resident GroupTable;
  • We introduced a Sequence field, Xseq and nSeq without it, the straight table does its own sorting, and by using straight tables we do not have control to suppress this required extra column.
  • The variable vNoBreakSpace was used to handle 5 non-breakable spaces avoiding extra typing.

The resulting straight table looks like this:

Group_Straight_Table_01.jpg

Hope this helps,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.