Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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()
May be share a sample of your data and then we might be able to help you by creating a sample?
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);
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:
Peter
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
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 >= ''
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.
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;