Values aggregation

[slribeiro]

Imagine I have the following data set

TABLE1
Dim1	Dim2	Dim3	Dim4	Value1	Value2
Name1	Country1	City1	-	100	-
Name2	Country2	City2	-	200	-
Name3	Country2	City3	-	300	-
Name4	Country3	City4	-	400	-
-	-	-	Country1	-	300
-	-	-	Country2	-	200
-	-	-	Country3	-	500

How can I aggregate this data set in order to be able to build the following pivot table (I don't mind to use set analysis). Any solution it works to me.

PIVOT
Dim1	Dim2	Value1	Value2
Name1	Country1	100	300
Name2	Country2	200	200
Name3	Country2	300	200
Name4	Country3	400	500

Please help me, I'm really stucked.

Thanks in advance

[sunny_talwar]

This is all in one table?

[Or]

Nothing in your data structure suggests there's a relation between the fifth row (Country1 / 300) and the first row, or any other set of rows. Since you have not specified what the relationship is, I am going to assume that the relationship is based on Dim2 = Dim4. If that is the case, the most logical approach at the script level is:

1) Create a mapping table based on Dim1 and Dim2 (Load Distinct Dim1, Dim2)

2) Fill in Dim2 where it is missing (get the value from Dim4)

3) Lookup the appropriate Dim1 from your lookup table and fill it in

I would strongly advise against trying to solve this problem at the chart dimension level. That's not the right spot to fix your data structure.

[stigchel]

I would advice to change this in your load script something like:

Tmp:

LOAD Dim1,

     Dim2,

     Dim3,

     Dim4,

     Value1,

     Value2

FROM

[https://community.qlik.com/thread/230404]

(html, codepage is 1252, embedded labels, table is @1, filters(

Remove(Row, Pos(Top, 1))

));

NoConcatenate Load Dim1,Dim2,Dim3,Value1 resident Tmp where Len(Dim1)>0;

Join Load Dim4 As Dim2, Value2 resident Tmp where Len(Dim4)>0;

Drop Table Tmp;

[stigchel]

And the document attached