Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Looks like you need a Generic Load.
Have a look at this blog post http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/31/generic
Looks like you need a Generic Load.
Have a look at this blog post http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/31/generic
or with set analysis
only( {$<FIELDNAME={LENGTH}>} FIELDVALUE) * only( {$<FIELDNAME={WIDTH}>} FIELDVALUE)
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
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
Thanks Bill, Massimo, Anand and Ashfaq for your your responses; all of them work well.
Thanks again!
JT