topic Re: Variables' definition in QlikView

Variables' definition

Tue, 21 Jan 2014 08:40:27 GMT

Hi all,

Do anyone know how to use the result of a SQL sentence as the variable's value?

Thanks and best regards,

CoCo

Re: Variables' definition

Sokkorn — Tue, 21 Jan 2014 08:43:21 GMT

Hi CoCo,

What is your SQL sentence?

Regards,

Sokkorn

Re: Variables' definition

Sokkorn — Tue, 21 Jan 2014 08:45:18 GMT

Or Something like this

LET Query = 'SELECT * FROM TableName';

[TableName]:

LOAD *;

SQL $(Query);

Re: Variables' definition

Tue, 21 Jan 2014 08:45:31 GMT

CoCo,

1) Put the result into a temp table

2) use the peek function to store the result into a local variable

let MyVar = peek('FieldName', Integer Number, 'Table name')

The number starts at 0 (1st line) and ends at NoOfRows('Table')-1

Fabrice

Re: Variables' definition

Tue, 21 Jan 2014 08:51:41 GMT

Hi Scokkorn,

In the qlikview, is the SQL grammar same with standard SQL sentences?

Re: Variables' definition

Tue, 21 Jan 2014 08:54:48 GMT

Hi Fabrice,

Re: Variables' definition

Sokkorn — Tue, 21 Jan 2014 08:55:25 GMT

Yes, CoCo

SQL Select Field1, Field2 From TableName Where Field1='Value';

Regards,

Sokkorn

Re: Variables' definition

Tue, 21 Jan 2014 08:58:15 GMT

Hi Sokkorn,

Actually, I want to creat the variable, using the result of a SQL sentence, in the "Variable Overiew" window.

So how should I do ? Could you please provide a sample for me?

Thanks too much.

Re: Variables' definition

Sokkorn — Tue, 21 Jan 2014 09:00:16 GMT

Hi CoCo,

Can you prepare your SQL statement with result set? And manipulate your variable for output?

Regards,

Sokkorn

Re: Variables' definition

Tue, 21 Jan 2014 09:02:17 GMT

Oh, great!

But when I use the same sentence in the "Variable Overiew" window, it occurs the grammar errors.

I don't know why!

FYI:

Re: Variables' definition

Tue, 21 Jan 2014 09:04:50 GMT

You may compute the data directly during the script also and store the result into table.

You may also create lots of variables (MyVar1, MyVar2, MyVar ...) such a way:

let counter=1;

do while counter < NoOfRows('Meas')
MyVar$(counter)=peek('Measures', counter-1, 'Meas');
counter=counter+1;
loop

Fabrice

Re: Variables' definition

Tue, 21 Jan 2014 09:05:51 GMT

Hi Sokkorn,

Please see the above reply.

Re: Variables' definition

Sokkorn — Tue, 21 Jan 2014 09:07:00 GMT

Hi CoCo,

Here is sample one

//This statement is to fetch Max date from field DateField.

[MaxDateTable]:

LOAD Max(DateField) AS [MaxDateValue];

SQL Select DateField From TableName;

//Here is variable vMaxDate where it get value from table above with format YYYY-MM-DD. Ex. 2014-01-21

Let vMaxDate = Date(Peek('MaxDateValue',0,'MaxDateTable'),'YYYY-MM-DD');

Regards,

Sokkorn

Re: Variables' definition

jonathandienst — Tue, 21 Jan 2014 09:11:54 GMT

CoCo Xi wrote:

But when I use the same sentence in the "Variable Overiew" window, it occurs the grammar errors.

I don't know why!

I wouldn't worry about those. QV is trying to interpret the text as a QV expression, and the SQL is not a valid QV expression and QV will pass this to the database server for evaluation.

HTH

Jonathan

Re: Variables' definition

Sokkorn — Tue, 21 Jan 2014 09:12:45 GMT

Hi CoCo,

For this one, I would suggest you load all data from Account table. Like this

[Age]:

SQL Select [Account ID],[Account Name], [Age] From Account;

Create new variable in "Variable Overview" by click Add:

1. Variable name: vAge2635

2. Definition: Count({$<[Age]={'26-35'}>} [Account ID])

Regards,

Sokkorn

Re: Variables' definition

Tue, 21 Jan 2014 09:15:39 GMT

Oh, how to definite the variable using the result of a SQL sentence correctly?

Re: Variables' definition

Tue, 21 Jan 2014 09:18:25 GMT

Oh, great , thanks very much!

But is this the simplest method ? As I'll create many variables like vAge2635.

Re: Variables' definition

Tue, 21 Jan 2014 09:42:27 GMT

Oh, dear!

It works. Thank you!!!

CoCo