topic Re: Calculating values in a report in QlikView

Calculating values in a report

Tue, 04 Nov 2014 18:12:29 GMT

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.

Re: Calculating values in a report

Anonymous — Tue, 04 Nov 2014 18:20:13 GMT

Looks like you need a Generic Load.

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

Re: Calculating values in a report

maxgro — Tue, 04 Nov 2014 18:53:29 GMT

or with set analysis

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

Re: Calculating values in a report

its_anandrjs — Tue, 04 Nov 2014 19:19:27 GMT

Hi,

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

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

Re: Calculating values in a report

ashfaq_haseeb — Tue, 04 Nov 2014 19:19:40 GMT

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:

Expression 1:

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

Expression 2:

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

Regards

ASHFAQ

Re: Calculating values in a report

Wed, 05 Nov 2014 11:56:29 GMT

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

Thanks again!