Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have an issue representing my data in a table. My data is basically structured like this:
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 |
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:
Dimension | Description (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?
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;
The resulting straight table looks like this:
Hope this helps,
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;
The resulting straight table looks like this:
Hope this helps,