topic Re: Using variables in Load statement in QlikView

Using variables in Load statement

Mon, 28 May 2012 10:48:09 GMT

Hi , I'm trying to limit the data that gets loaded into a view. I have created a couple of variables that represent outstanding quantity and un-invoiced quantity. However , when I try and included them in the load ( if either are greater than zero ) they are not recognised. When I look at the variables available in the script they do not appear. Is it not possible to do this or have I not defined them correctly ? Many thanks. Simon

Re: Using variables in Load statement

Miguel_Angel_Baeyens — Mon, 28 May 2012 10:52:47 GMT

Hi Simon,

It's difficult to give the right answer without checking the code, but anyway, yours should look like this

// Variable definition and assignment
SET vHighLimit = 3000;
SET vLowLimit = 50;
// WHERE clause in the LOAD statement using both variables
Invoices:
LOAD InvoiceID,
     CustomerID,
     Date,
     Amount
WHERE Amount >= $(vLowLimit) AND Amount <= $(vHighLimit);
SQL SELECT InvoiceID,
     CustomerID,
     Date,
     Amount
FROM db.Table;

Hope that helps.

Miguel

Re: Using variables in Load statement

Sat, 30 Mar 2013 08:47:14 GMT

Just to make things right, there is one small mistake:

Instead of

WHERE Amount >= $(vLowLimit) AND Amount <= $(vHighLimit);

you shoud write

WHERE Amount >= '$(vLowLimit)' AND Amount <= '$(vHighLimit)';

Re: Using variables in Load statement

Miguel_Angel_Baeyens — Sat, 30 Mar 2013 09:05:49 GMT

Hi,

Thanks for noting, but that will only apply if the WHERE Amount is expecting a literal or a string, if it's expecting a numeric value, then you must not use the quotes. And since the Amount field usually stores numeric values, therefore my use of the variables without quotes.

Actually, depending on the driver and the database, using the quotes is casting the value in the variable to string, and it might make the load slower, as the driver has to prior cast the value to numeric, then compare to match the WHERE clause.

Regards,

Miguel

Re: Using variables in Load statement

Thu, 14 May 2015 09:23:59 GMT

before using the variable in the load statement of script, set a field level trigger on the particular field whose value will be stored in variable.

Add on select action as set variable.

Give the variable name. and in expression mention the field name.

using it in the load statement will fetch the desired results.