Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Dynamically assign SQL Database based on date

I have a reference table in SQL that automatically updates so that you can pick yesterday's snapshot - UNIXAKCCollection.dbo.KC_Named. However, when I run the code below and try to use a variable and the Peek command the field results are null. Any help would be greatly appreciated.

Thanks

JP

//Dataset selects Yesterday's snapshot

YesterDatabase:

SQL  Select PhysicalName as DBName

From UNIXAKCCollection.dbo.KC_Named as N

Inner Join UNIXAKCCollection.dbo.KC_Databases D on N.NameID = D.NameID

WHERE N.DatabaseID =1

  and N.DatabaseTag =1;

Let vdatabase = Peek('DBName','YesterDatabase'); //However vdatabase variable is null

SQL select * from $(vdatabase).larmstr;

Tags (3)
1 Solution

Accepted Solutions
vishsaggi
Esteemed Contributor III

Re: Dynamically assign SQL Database based on date

Try this?

Let vdatabase = Peek('DBName', -1,'YesterDatabase');

9 Replies
vishsaggi
Esteemed Contributor III

Re: Dynamically assign SQL Database based on date

Try this?

Let vdatabase = Peek('DBName', -1,'YesterDatabase');

Not applicable

Re: Dynamically assign SQL Database based on date

That worked well. Thanks. However, I have a new error when I try the next select clause:

ODBC read failed

SQL select *  from UNIXASnapshot20170403.dbo.larmstr

Note that I added in the dbo in the table name.

Thanks

vishsaggi
Esteemed Contributor III

Re: Dynamically assign SQL Database based on date

Are you using variable in that next select script?

Not applicable

Re: Dynamically assign SQL Database based on date

Yes and when I copy the text of the error message and run it it SSMS it works fine.

vishsaggi
Esteemed Contributor III

Re: Dynamically assign SQL Database based on date

Can you tell what variable is holding?

May be try this?

SQL select * from '$(vdatabase)'.larmstr;

Not applicable

Re: Dynamically assign SQL Database based on date

Thanks, Vishwarath. That worked great. Thanks for the additional input. I realized that I had omitted a change in my code which I have corrected and now all parts of the script work.

vishsaggi
Esteemed Contributor III

Re: Dynamically assign SQL Database based on date

So which one worked?

Not applicable

Re: Dynamically assign SQL Database based on date

This syntax worked to assign the variable

Let vdatabase = Peek('DBName', -1,'YesterDatabase');


And this syntax worked for the query:

SQL select * from $(vdatabase).dbo.larmstr;

Thanks

vishsaggi
Esteemed Contributor III

Re: Dynamically assign SQL Database based on date

Glad it worked.