Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Discussion Board for collaboration related to QlikView App Development.

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Help me define expression in straight table

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Not applicable

2014-09-29
12:58 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Help me define expression in straight table

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

- Tags:
- new_to_qlikview

659 Views

1 Solution

Accepted Solutions

Anonymous

Not applicable

2014-10-01
08:19 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 | f3*f4 |
---|---|---|---|---|

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 |

437 Views

7 Replies

Anonymous

Not applicable

2014-09-29
04:15 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

437 Views

m_woolf

Master II

2014-09-29
04:18 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

437 Views

Not applicable

2014-09-30
02:38 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

437 Views

Anonymous

Not applicable

2014-09-30
07:51 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 | f3*f4 |
---|---|---|---|---|

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 |

437 Views

Not applicable

2014-09-30
09:35 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

437 Views

Anonymous

Not applicable

2014-10-01
08:19 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 | f3*f4 |
---|---|---|---|---|

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 |

438 Views

Not applicable

2014-10-02
02:39 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Yes, INPUTFIELD functionality solves my problem!

Casey, many thanks!

437 Views