Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
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
MVP & Luminary
MVP & Luminary

Re: Straight Table with dynamic values for specific columns

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
MVP & Luminary
MVP & Luminary

Re: Straight Table with dynamic values for specific columns

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

Not applicable

Re: Straight Table with dynamic values for specific columns

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