Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Straight Table with dynamic values for specific columns

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:

IDDescription
1Bla Bla Bla
2Bla 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:

A1A2A..B1B2B..
7201012
323012
01411

1

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

IDDescriptionSum Ax where x = IDSum1 Bx where x = ID
1Bla Bla Bla101
2Bla Bla Bla Bla23

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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)


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
Gysbert_Wassenaar

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)


talk is cheap, supply exceeds demand
Not applicable
Author

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:

ColourAgeA1A2A..B1B2B..
RedOld7201012
RedYoung323012
YellowYoung01411

1