topic Re: Use Load output as Variable for next Load in QlikView

Use Load output as Variable for next Load

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

Just downloaded QlikView for testing some front end reporting tools. So I'm just new an do some try and error.

Now I have a question about Variables. I've made some SQL queries in the main Script window, now I want to use one result as a Variable for an upcoming Query.

So I made a Connection to A SQL server, made some queries, one is "LOAD invdat;". The next Query needs the output of this one. So I made:

LOAD LiefDatum;

SQL SELECT distingt LiefDatum

FROM sqldbdepot.dbo."dep_ebp"

where LiefDatum >= $(invdat) ;

Wanted to use the invdat as Variable, but that's not working the way I thought. So I need to declare that Variable somehow and put the output of the first Query in that Variable. But how?

Re: Use Load output as Variable for next Load

sunny_talwar — Thu, 01 Sep 2016 16:26:34 GMT

Is this a single date? You will need to use Peek() statement to make this work. You can look at the MasterCalendar script to see how to save a value into a variable using Peek() function

Creating A Master Calendar‌

Re: Use Load output as Variable for next Load

Fri, 02 Sep 2016 03:57:06 GMT

Thanks for you anser.

It is called Date, but it is just one 8 digit number. The first Query is searching for the last date of invetory. So that gives only one result. Result looks like 20160902 < date from today.

Don't understand how Peek could help.Missing Manual - Peek()

Re: Use Load output as Variable for next Load

sunny_talwar — Fri, 02 Sep 2016 11:10:09 GMT

May be share a sample of your data and then we might be able to help you by creating a sample?

Re: Use Load output as Variable for next Load

Fri, 02 Sep 2016 11:36:10 GMT

Yes, of course. This is what I have at this moment. It is not much at this time, as you see, first the connection to a MS-SQL Server 2000. Next a Query for the Date, as mentioned one result, 8 digits. And finally a query for a list beginning whit the result of the first query.

The KFZ and Artikelnummer are Variables I gave at the Form-window in an input-box.

When I run it, it will do the first part. Than hang at the second, leaving the variable in the line empty: "where LifeDatum >= "

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=sqldbdepot;Data Source=Navision-server;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=AP-2-4-4;Use Encryption for Data=False;Tag with column collation when possible=False];

LOAD invdat;

SQL SELECT max(UPD_DAT) as invdat

FROM sqldbdepot.dbo."dep_inv"

where Lagernr = $(KFZ) and

artnr = $(Artikelnummer);

LOAD LiefDatum;

SQL SELECT distingt LiefDatum

FROM sqldbdepot.dbo."dep_ebp"

where LiefDatum >= $(invdat);

Re: Use Load output as Variable for next Load

Peter_Cammaert — Fri, 02 Sep 2016 11:37:06 GMT

Peek() lets you have a look in a single cell of a single row in a table. It's about the only function that allows you to get a value from a table and store it into a variable.

Let's say you want to have a cut-off date for the next load and its value shoyuld be the maximum date from the previous load? Then Sunny's MasterCalendar example will to the trick. The steps are:

Load the maximum value of invdat from the resident table that was created by the previous Query. In QV, dates have both a string and a number representation. That's why you can get the maximum date.
Use peek to get that maximum date from the table cell into a variable. The Temp table in Sunny's exampel has only one row, so finding the maximum value won't be much of a problem.
$-substitute this maximum value into your next query (make sure it is transformed into a valid SQL date value) and send the query to your RDBMS.

Peter

Re: Use Load output as Variable for next Load

Peter_Cammaert — Fri, 02 Sep 2016 14:57:49 GMT

That is to be expected because a variable is not the same as a field or a column in a table. They can have the same name, but since QlikView Script is highly context-dependent, they are still different "objects". Which one QlikView script will use depends on the context of the expression. Inside a LOAD statement, invdat will always refer to a column in the source or a field in your data model. Outside of a LOAD statement, invdat will usually refer to a variable (even if it doesn't exist). Inside a $-sign substitution, invdat will refer to a variable except when using an expression. It becomes much more complex if you think about this any further.

One golden rule (best practice): give your variables names that can be easily recognised for their type. That's why in this community, you'll often see variable names like vTemporaryNumber, or vMaxValue. The v tells the developer that this name should only be used in a variable context.

Between the first load and the second load, you'll have to find the maximum value of column/field invdat and store this value into a new variable called... invdat. That's where you'll use Sunny's MasterCalendar-like technique.

You can insert something like this after the first LOAD/SQL SELECT:

// Note: this LOAD can be omitted if the first table already

// offers a single max value

MaxVal:

LOAD max(invdat) AS MaxInvDat

RESIDENT Table1; // You should name your 1st table 'Table1'

LET vMaxInvDat = Date(peek('MaxInvDat')); // Convert to correct SQL format

DROP Table MaxVal;

and then you can go on like:

LOAD LiefDatum;

SQL SELECT distingt LiefDatum

FROM sqldbdepot.dbo."dep_ebp"

where LiefDatum >= '$(vMaxInvDat)'; // numerical QV dates may not work here

Make sure that the vMaxInvDat value format is acceptable for the last SQL statement !

Peter

Re: Use Load output as Variable for next Load

Mon, 12 Sep 2016 13:08:44 GMT

RESIDENT Table1; gives an error that the table is not found.

But when you say it could be omitted, I think i don't need id. But do I need the Date Peek function? The InvDat and LiefDatum have the same format (YYYYMMDD). Even when I put Let vMaxInvDat = Date(peek('invdat')); in there, it won't work.

Somehow the query/error message looks like this:

LOAD LiefDatum;

SQL SELECT distingt LiefDatum

FROM sqldbdepot.dbo."dep_ebp"

where LiefDatum >= ''

Re: Use Load output as Variable for next Load

Peter_Cammaert — Tue, 13 Sep 2016 07:35:13 GMT

If we are going to refer to previously loaded tables, then you should name them (by assigning a label in front of the Table LOAD). I used Table1 as an example name, because you forgot to assign any other names. Use whatever name you like.

Re: Use Load output as Variable for next Load

Tue, 13 Sep 2016 09:45:25 GMT

Somehow i'm not able to create tables. It always sais "Table not Found". This could be do because i'm running on a SQL 2000 server.

But I tried the Debug function. I could see, at the point I reach the "Let vMaxInvDat = date(peek('invdat'));" statement that Invdat = <NULL>

Could this also be a problem from the SQL 2000 Server?

What i did now, is to combine those two queries into one, this works and looks like this:

LOAD LiefDatum;

SQL

Declare @Datum char(8)

Set @Datum = (SELECT max(UPD_DAT)

FROM sqldbdepot.dbo."dep_inv"

where Lagernr = $(KFZ) and

artnr = $(Artikelnummer))

SELECT distinct LiefDatum

FROM sqldbdepot.dbo."dep_ebp"

where LiefDatum >= @Datum;

Re: Use Load output as Variable for next Load

Peter_Cammaert — Tue, 13 Sep 2016 15:50:55 GMT

At the end of a succesful script run, press Ctrl-T to open the Table Viewer. There will be at least one table with at least one field. If you do not assign names, QlikView will.