Calculating values in a report

[#1]

Hi all:

I have a table with the following columns

ID | FIELDNAME | FIELDVALUE

1   |  MATERIAL  | GRAPHENE

1   |  LENGTH     |  10

1   |  WIDTH       |  20

1   |  HEIGHT     |  3

2   |  MATERIAL  | METAL

2   |  LENGTH     |  10

2   |  WIDTH       |  20

2   |  HEIGHT     |  3

3   |  MATERIAL  | SHEET METAL

3   |  LENGTH     |  10

3   |  WIDTH       |  20

3   |  HEIGHT     |  3

4   |  MATERIAL  | ALUMINUM

4   |  LENGTH     |  10

4   |  WIDTH       |  20

4   |  HEIGHT     |  3

I am trying to create a report that will give me the following

ID | MATERIAL         | AREA (LENGTH * WIDTH)    | VOLUME (LENGTH * WIDTH* HEIGHT)

1  | GRAPHENE         | 200                                        | 600

2  | METAL                 | 200                                        | 600

3  | SHEET METAL      | 200                                        | 600

4  | ALUMINUM          | 200                                        | 600

What is the best way to approach this?  Any help is appreciated.

Thanks

JT.

[Anonymous · Accepted solution]

Looks like you need a Generic Load.

Have a look at this blog post http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/31/generic

[maxgro]

or with set analysis

only( {$<FIELDNAME={LENGTH}>} FIELDVALUE) * only( {$<FIELDNAME={WIDTH}>} FIELDVALUE)

[its_anandrjs]

Hi,

Or if in the load script you can extract this fields and used with variables

A:

LOAD * INLINE [

    ID, FIELDNAME, FIELDVALUE

    1, MATERIAL, GRAPHENE

    1, LENGTH, 10

    1, WIDTH, 20

    1, HEIGHT, 3

    2, MATERIAL, METAL

    2, LENGTH, 10

    2, WIDTH, 20

    2, HEIGHT, 3

    3, MATERIAL, SHEET METAL

    3, LENGTH, 10

    3, WIDTH, 20

    3, HEIGHT, 3

    4, MATERIAL, ALUMINUM

    4, LENGTH, 10

    4, WIDTH, 20

    4, HEIGHT, 3

];

New:

LOAD Distinct

ID ,

PurgeChar(FIELDVALUE,'0123456789') as Material

Resident A Where Len(PurgeChar(Trim(FIELDVALUE),'0123456789'))>0;

Join

LOAD Distinct

ID,

FIELDNAME,

KeepChar(FIELDVALUE,'0123456789') as FieldValue

Resident A Where Len(KeepChar(Trim(FIELDVALUE),'0123456789'))>0;

DROP Table A;

Create 3 Variables

v1 =Sum(DISTINCT  {<FIELDNAME = {'LENGTH'} >} FieldValue );

v2 =Sum(DISTINCT  {< FIELDNAME = {'WIDTH'} >}  FieldValue );

v3 =Sum(DISTINCT  {< FIELDNAME = {'HEIGHT'} >} FieldValue );

And in chart

Dim1:- ID

Dim2:-Material

Expre:- $v1) * $(v2)

Expre:- $(v1) * $(v2) * $(v3)

And straight chart

Regards

Anand

[ashfaq_haseeb]

Hi,

have a look at attached application

Script

oad * Inline
[
ID , FIELDNAME , FIELDVALUE
1   ,  MATERIAL  , GRAPHENE
1   ,  LENGTH     ,  10
1   ,  WIDTH       ,  20
1   ,  HEIGHT     ,  3
2   ,  MATERIAL  , METAL
2   ,  LENGTH     ,  10
2   ,  WIDTH       ,  20
2   ,  HEIGHT     ,  3
3   ,  MATERIAL  , SHEET METAL
3   ,  LENGTH     ,  10
3   ,  WIDTH       ,  20
3   ,  HEIGHT     ,  3
4   ,  MATERIAL  , ALUMINUM
4   ,  LENGTH     ,  10
4   ,  WIDTH       ,  20
4   ,  HEIGHT     ,  3
];

New chart Straight table

Dimension:

ID

Expression 1:

only( {$<FIELDNAME={MATERIAL}>} FIELDVALUE)

Expression 2:

only( {$<FIELDNAME={LENGTH}>} FIELDVALUE) * only( {$<FIELDNAME={WIDTH}>} FIELDVALUE)

Regards

ASHFAQ

[#6]

Thanks Bill, Massimo, Anand and Ashfaq for your your responses; all of them work well. 

Thanks again!

JT