Straight Table with dynamic values for specific columns

[#1]

Dear all,

I didn't use Qlikview for a while.. so, I'm sorry if this is a silly question.

I have one source file that looks like this:

MainData:

ID	Description
1	Bla Bla Bla
2	Bla Bla Bla Bla
..	Bla Bla Bla Bla ...

Let's see that the above table contains X rows, with an ordered ID number.

Then, I have another table/source file like this below:

MyNumbers:

A1	A2	A..	B1	B2	B..
7	20	1	0	1	2
3	2	3	0	1	2
0	1	4	1	1	1

What I want as final result is a Straight Table like this:

ID	Description	Sum Ax where x = ID	Sum1 Bx where x = ID
1	Bla Bla Bla	10	1
2	Bla Bla Bla Bla	23	3
..	Bla Bla Bla Bla ..	8	5

Basically, if my ID is X.. then I want to calculate the Sum(AX) and the Sum(BX).

for ID = 1, I'll sum the value of columns A1 (that's 10) and B1 (that's 1). For ID = 100, I'll sum A100 and B100.

What can be the best approach which will work for hundreds of IDs?

Thanks for any help.

BR,

Giuseppe

[Gysbert_Wassenaar · Accepted solution]

Use this script:

Table1:

LOAD ID,

     Description

FROM

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

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

Temp:

CrossTable(Temp, Value)

LOAD

     1 as dummy,

    *

FROM

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

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

Table2:

LOAD

     Left(Temp,1) as AorB,

     Mid(Temp,2) as ID,

     Value

RESIDENT

  Temp;

DROP TABLE Temp;     

Then create a straight table with ID and Description as dimensions and two expressions:

Sum of Ax: sum({<AorB={'A'}>}Value)

Sum of Bx: sum({<AorB={'B'}>}Value)

[#3]

Thanks a lot! It works!

However, in this way my final table it is no longer "dynamic", based on the other dashboards that I created. But it is my fault. I simplied too much the example to understand the concept."MyNumbers" table, in the reality, contains several other headers/columns. Now, I need to figure out how to have the final table updated based on the selection of some values in the headers/columns not included in the first example (Sum only for Colour Red... or for Young.. or Red&Old).

However, I think that I just need to play with the crosstable to figure out how to do that. Thanks again!

MyNumbers:

Colour	Age	A1	A2	A..	B1	B2	B..
Red	Old	7	20	1	0	1	2
Red	Young	3	2	3	0	1	2
Yellow	Young	0	1	4	1	1	1