Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
AmCh
Creator
Creator

Calculation within Tables

Hi all,

I have the following table (Input) as an example:

abcdefhi
1601,12,1009
2002,33,21295,8
3003,44,301,250
4004,55,403,330
5005,66,5102,43
6016,77,60024,56
00000000

 

Desired intermediate output (Table):

respecting those formula:

j=m*c (m: entered value from the user with an input box)

k=n*d (n: entered value from the user with an input box)

l=e

vMAX=MAX(j,k,l)

respecting this formula, desired first output looks like:

P[i][t]=(vMax*t-x)(1-f)+(((t-(1/vMax))/o)+1)*f // x=0 if t=o else x=0,001

i/t123456...o
1P[1][1]       
2        
3        
4      P[i][t] 
5        
6       P[6][o]

 

respecting to this formula, second desired output looks like:

q
SUM(h*Transpose(P[1][t])
 
 
 
 
SUM(h*Transpose(P[6][t])

 

Any Help please?

 

Thanks in advance

1 Solution

Accepted Solutions
edwin
Specialist III
Specialist III

this is what i did

View solution in original post

13 Replies
edwin
Specialist III
Specialist III

some missing info:  where do you get fields t, x, and o?

AmCh
Creator
Creator
Author

o= given from the user with an input box

t increments for example within a for loop from the value 1 to o. (For t=1 to o)

t=o (not zero) -> x=0 (zero), otherweise x=0,005

edwin
Specialist III
Specialist III

create a pivot table and use values loops as dimensions in place of i and t in your expression:

t:  =valueloop(0,$(o))  - this will generate fields in your table 

the problem though with your expression is that you are not aggregating your fields:

P[i][t]=(vMax*t-x)(1-f)+(((t-(1/vMax))/o)+1)*f

even vMax is not aggregated and since you have multiple values (multiple rows) this expression will be null.  unless you either add all your fields or the user selects one row from your table containing fields a..i

AmCh
Creator
Creator
Author

Thank you very much for your prompt reply.

Since my expression does not need aggr. functions, I need to group by a created field with the valueloop to have different values.

The generation of the columns is manually doable, but how can I generate multiple columns with a loop?

How can this code look like?

Thanks in advance

edwin
Specialist III
Specialist III

your expression P[i][t] - does the i refer to the field?  and t refers to a generated number from 0 to o?

value loop will generate the individual values you need for t.  there really is no loop in the traditional sense of loops (like for next or do while)

in your expression, you need to generate a matrix of values (i, t).  where i is the field and t is from 0 to o (entered by user)

in your pivot chart, create 2 dimensions:

i -> this is the field
valueloop(0,$(o)) -> this is t

in your expression, replace all t fields with valueloop(0,$(o)) 

you dont need to group by i and t value loop, also encapsulate your vMax variable with $() - $(vMax)
vMax should not be immediately evaluated the expression should be 
rangemax(m*c, n*d, e)  
this way you evaluate all values for c, d, and e

 

edwin
Specialist III
Specialist III

here is a sample pivot:

edwin_0-1611170585815.png

 

using your data i and valueloop(0,$(o)), where o =5
in the expression i used 
=ValueLoop(0,$(o)) * i

just to prove the use of valueloop
you can then expand on that

Ott25456
Contributor
Contributor


@AmCh wrote:

Hi all,

I have the following table (Input) as an example:

abcdefhi
1601,12,1009
2002,33,21295,8
3003,44,301,250
4004,55,403,330
5005,66,5102,43
6016,77,60024,56
00000000

 

Desired intermediate output (Table):

respecting those formula:

j=m*c (m: entered value from the user with an input box)

k=n*d (n: entered value from the user with an input box)

l=e

vMAX=MAX(j,k,l)

respecting this formula, desired first output looks like:

P[i][t]=(vMax*t-x)(1-f)+(((t-(1/vMax))/o)+1)*f // x=0 if t=o else x=0,001

i/t123456...o
1P[1][1]       
2        
3        
4      P[i][t] 
5        
6       P[6][o]

 

respecting to this formula, second desired output looks like:

q
SUM(h*Transpose(P[1][t])
 
 
 
 
SUM(h*Transpose(P[6][t])

 

Any Help please?

 

Thanks in advance


Table Calculations and Calculated Fields are similar in the sense that they both use functions to compute the results. The difference is how and where the computing takes place, where the result is saved and if it can be reused in more worksheets.

AmCh
Creator
Creator
Author

I tried it, but I didn't get it:

Dimensions: i and t as ValueLoop from 1 to 5

AmCh_0-1611248127490.png

and the expression as your example:

t*i

AmCh_1-1611248213133.png

I got this 'table':

AmCh_2-1611248331448.png

 

Where is the problem here?

Thanks in advance.

 

AmCh
Creator
Creator
Author

actually I need these calculations as an intermediate step to further considerations. These results have not to be shown.