Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI
I have the two first different tables that I need to be linked, and where Dim2 Name2 from Table2 "B & C" need´s to collapse into A in Table1 in the first table, resulting in the last table, BUT I still need the information/granularity in Table2:
Should I use generic keys in this case?
See if the following is getting you what you were aiming for:
Table2:
LOAD * INLINE [
%ID number, Dim1 Number, Dim2 Name2, Measure
567|1|A, 1, A, 90
567|1|B, 1, B, 10
567|1|C, 1, C, 50
567|2|D, 2, D, 20
];
Table1:
LOAD [Dim1 Number],
FirstValue([Dim2 Name2]) as [Dim2 Name],
FirstValue([%ID number]) as [%ID number],
Sum(Measure) as TotalMeasure
Resident Table2
Group By [Dim1 Number];
Best,
Sunny
See if the following is getting you what you were aiming for:
Table2:
LOAD * INLINE [
%ID number, Dim1 Number, Dim2 Name2, Measure
567|1|A, 1, A, 90
567|1|B, 1, B, 10
567|1|C, 1, C, 50
567|2|D, 2, D, 20
];
Table1:
LOAD [Dim1 Number],
FirstValue([Dim2 Name2]) as [Dim2 Name],
FirstValue([%ID number]) as [%ID number],
Sum(Measure) as TotalMeasure
Resident Table2
Group By [Dim1 Number];
Best,
Sunny
Thank you , interesting solution, though not really what I was looking for, but it does what I asked for, so I give a correct answer for the effort. It is in my case two different tables, and I also realize that I did not describe my case correct, thats not your fault. I had hoped for some kind of hierarchy solution, that B and C is a subpart of A. I will look a bit more on the hierarchy functions.