Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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;

1 Solution

Accepted Solutions
Highlighted
Champion III
Champion III

Try this?

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

View solution in original post

9 Replies
Highlighted
Champion III
Champion III

Try this?

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

View solution in original post

Highlighted
Not applicable

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

Champion III
Champion III

Are you using variable in that next select script?

Highlighted
Not applicable

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

Highlighted
Champion III
Champion III

Can you tell what variable is holding?

May be try this?

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

Highlighted
Not applicable

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.

Highlighted
Champion III
Champion III

So which one worked?

Highlighted
Not applicable

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

Highlighted
Champion III
Champion III

Glad it worked.