Dear Qlik community, I would like to create a sum over several levels with a mapping file and currently do not know how to implement this.
My data table looks like this: Column A: Unit Column B: From unit Column C: Amount
Unit
from Unit
Amount
A
B
A
C
A
D
B
C
B
D
B
5
C
D
C
E
C
30
D
50
E
100
X
D
X
C
The sum should now be formed up to the highest unit that is shown. In our example of unit A and unit X.
For unit A, the amount would be (B+C+D) B is calculated from amount (C+D) and has an amount of 5 C is calculated from amount (D+E) and has an amount of 30 D has an amount of 50
In this respect: A = ((C+D+5) + (D+E+30) + (D)) = (((D+E)+D+5) + (D+E+30) + (D)) = (((50+100)+50+5)+(50+100+30)+(50))
For X, the equivalent would only be D and C
Unfortunately, I have no idea how to construct a formula that uses the conditions from the "From unit" column for the sum.
I look forward to any helpful tips and thank you for your efforts!
You can solve this issue with an Interval Match. I think you'll need two tables: one table without the amounts (where the amounts are empty) and another table with only the amounts and the Unit field (where the amounts are not empty). And you will need to convert the letters to numerical values, for example A=1, B=2, etc.
