sum level higher in straight table

[#1]

In my case, source table

Level	Header 2
level1	10
level2	4
level3	5
level1	1
level2	3

Difined level relation as level1 => level2 => level3

I need the straight table like blow

Level     sum

level1    23        // sum({<Level={'level1','level2','level3'}>} num)   10+1+4+3+5

level2    12        // sum({<Level={'level2','level3}>} num)   4+3+5

level3     5         //sum({<Level={'level3'} num)     5

Thanks for any help.

[sunny_talwar]

May be using a Link Table

Table:

LOAD Level,

  KeepChar(Level, '0123456789') * 1 as LevelNum,

    [Header 2]

FROM

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

(html, codepage is 1252, embedded labels, table is @1);

LinkTable:

LOAD 'Level' & LevelNum as ReportLevel,

  LevelNum + IterNo() - 1 as LevelNum

Resident Table

While LevelNum + IterNo() - 1 <= 3;

[#3]

Unfortunately I have some other expression in straight table use dimension "Level",so I can't change the dimension.

It's my mistake that forget attension this condition on the question.

Thanks for your help.

[sunny_talwar · Accepted solution]

Create a new flag in the script like this and also created another field which I don't want to do an accumulated sum

Table:

LOAD Level,

  KeepChar(Level, '0123456789') * 1 as LevelNum,

    [Header 2],

    Ceil(Rand() * 100) * 10 as Sales

FROM

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

(html, codepage is 1252, embedded labels, table is @1);

LinkTable:

LOAD 'Level' & LevelNum as ReportLevel,

  LevelNum + IterNo() - 1 as LevelNum,

If(LevelNum + IterNo() - 1 = LevelNum, 1, 0) as Flag

Resident Table

While LevelNum + IterNo() - 1 <= 3;

Dimension:

ReportLevel

Expressions

1) =Sum([Header 2])

2) =Sum({<Flag = {1}>}Sales)

Anywhere you want to do a calculation based of Level, use Flag = {1} in your expression

[#5]

Thanks very very very much.

I have a long way to go.