Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
This is all in one table?
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.
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;
And the document attached