topic Re: Help me define expression in straight table in QlikView

Help me define expression in straight table

Mon, 29 Sep 2014 16:58:21 GMT

I have a source table like this:

t1:

load * inline [

f1, f2, f3

A, X, 100

A, Y, 200

A, Z, 220

B, X, 250

B, Y, 150

...

];

and I define a set of variables as number of possible combinations of fields f1 and f2:

Let vAX = 1;

Let vAY = 2;

Let vAZ = 3;

Let vBX = 5;

... and so on ...

Now I build the straight table:

Dimension1 is f1

Dimension2 is f2

Expression1 is f3

and I want to define expression2 as:

<variable>*f3

where <variable> is the appropriate variable depending on the values of the fields f1 and f2, ie if f1='B' and f2='Y' then <variable> must be vBY

please help me ..

thanks in advance,

Oleg

Re: Help me define expression in straight table

Anonymous — Mon, 29 Sep 2014 20:15:53 GMT

I wouldn't recommend using variables in that way. If you are going to do that, use SET instead of LET. I would approach like this:

t1:

load * inline [

f1, f2, f3

A, X, 100

A, Y, 200

A, Z, 220

B, X, 250

B, Y, 150

...

];

t2:

LOAD DISTINCT

f1,

f2,

'v'&f1&f2 as Variable,

Count( 'v'&f1&f2) as Variable_Count

Resident t1

Group By f1, f2;

Then a table box like this could be created:

f1	f2	f3	Variable	Variable_Count
A	X	100	vAX	1
A	Y	200	vAY	1
A	Z	220	vAZ	1
B	X	250	vBX	1
B	Y	150	vBY	1

This will connect f1 and f2 to the field "Variable_Count" which defines the number of times each combination of f1 and f2 appears. Notice the text field "Variable" is no longe really necessary. Your expression2 would become:

[Variable_Count]*f3

Re: Help me define expression in straight table

m_woolf — Mon, 29 Sep 2014 20:18:21 GMT

As long as you don't have too many possible combinations, you might try doing this in script something like:

If(F1='A' and F2 = 'X',1,

if(F1='A' and F2 = 'Y',2,

if(F1='A' and F2 = 'Z',3,

. )))) as Multiplier

Then in your expression: =Multiplier * F3

Re: Help me define expression in straight table

Tue, 30 Sep 2014 06:38:04 GMT

Hello, Casey!

Thanks for the reply.

Maybe I did not clearly describe the problem, but I need the following:

To begin, consider a simplified example: I have only one variable, such as vVar1.
Then the expression2 has the form f3*$(=vVar1).

I want to keep the exact formula for expression2.
The difficulty is that I have a lot of variables (exactly equal to the number of combinations of fields f1 and f2) and must be substituted into the formula corresponding variable depending on the combinations of fields f1 and f2.

Regards,

Oleg

Re: Help me define expression in straight table

Anonymous — Tue, 30 Sep 2014 11:51:53 GMT

OK sorry I misunderstood you a bit. What do you mean by "the number of combinations of fields f1 and f2"? If that statement is taken literally then it wouldn't be a different value for every record in t1 (referencing your original post). It would just be 5 because there are 5 distinct combinations of values in f1 and f2 (AX, AY, AZ, BX, and BY). To me, it still sounds like you're saying this "variable" is counting the number of times a given combination appears in the data set. For example, if your source looked like this:

t1:

load * inline [

f1, f2, f3

A, X, 100

A, X, 200

A, X, 300,

A, Y, 200

];

Then vAX=3 and vAY=1...?

Regardless of the variable's meaning, I still think using variables is the wrong approach. That is not really a proper use of variables. It is extremely cumbersome and an extremely inefficient way to connect and store data. There has to be some connection between this "variable" and the dimensions (f1 and f2) if you want to use them in a chart. The way to connect them is a table. So if you are intent on hardcoding the value of "the number of combinations of fields f1 and f2" for each combination without a calculation, you could do this in the script:

t1:

LOAD * INLINE [

f1, f2, f3

A, X, 100

A, Y, 200

A, Z, 220

B, X, 250

B, Y, 150

];

t2:

LOAD * INLINE [

f1, f2, f4

A, X, 1

A, Y, 2

A, Z, 3

B, X, 5

B, Y, 7

];

t1 and t2 are joined on fields f1 and f2. So this straight table could be created:

f1	f2	f3	f4	*f3f4**
A	X	100	1	100
A	Y	200	2	400
A	Z	220	3	660
B	X	250	5	1250
B	Y	150	7	1050

Re: Help me define expression in straight table

Tue, 30 Sep 2014 13:35:56 GMT

Casey,

in general, the field f1 may contain a certain SET1 of symbols of N1 elements, field f2 may contain a certain SET2 of symbols of N2 elements, so the number of combinations is N1*N2.

In script within a nested for-loop I define N1*N2 variables. The name of each variable is generated by the rule:

1. prefix "v"

2. symbol from SET1

3. symbol from SET2

Why are so many variables? Because the user can change them within the application without script reload.

So every time user change any variable must immediately change the straight table (expression2 column) in those rows where the values of the fields f1, f2 correspond to the name of the modified variable.

Regards,

Oleg

Re: Help me define expression in straight table

Anonymous — Wed, 01 Oct 2014 12:19:56 GMT

OK I can't come up with a way to dynamically define the name of a variable when calling it in a chart expression. How about instead of using a loop to create a variable for every combination of f1 and f2, you use a loop to fill a field an input field f4 in a second table t2? Using the INPUTFIELD function allows the user to redefine the fields value without reloading the script. Script would be something like:

INPUTFIELD f4;

t1:

load * inline [

f1, f2, f3

A, X, 100

A, Y, 200

A, Z, 220

B, X, 250

B, Y, 150

];

//SOME LOOP to create t2...result:

t2:

f1	f2	f4
A	X	1
A	Y	2
A	Z	3
B	X	4
B	Y	5

So f4 is has a value connected to each combination of f1 and f2 and it can be changed by the user without script reload. Then this straight chart could be created.:

f1	f2	f3	f4	*f3f4**
A	X	100	1	100
A	Y	200	2	400
A	Z	220	3	660
B	X	250	4	1000
B	Y	150	5	750

Re: Help me define expression in straight table

Thu, 02 Oct 2014 06:39:41 GMT

Yes, INPUTFIELD functionality solves my problem!

Casey, many thanks!