topic Translate qv into sql code in QlikView

Translate qv into sql code

Sat, 06 Nov 2010 14:54:11 GMT

Dear All,

I need to translate the below qv into sql code. The idea behind is that there is a number of databases each including a given Table that I need to source from. Because the number of dbs is high and dynamic, I use a for...next loop and a table with all dbs names (dbo_Configuration.dbo.Bases) to query them. Now, I need to translate it into sql using "while", and, got stuck. Could you please help preparing it?

Bases: SELECT Base_Name AS Base FROM dbo_Configuration.dbo.Bases

LET NoOfBases = NoOfRows('Bases');

FOR i=0 TO $(NoOfBases)-1

LET B = peek('Base',$(i),'Bases');

LET C = 'dbo_'& peek('Base',$(i),'Bases')& '.dbo.Table

SELECT *

FROM $(C);

NEXT

BR,

Przemek

Translate qv into sql code

prieper — Sun, 07 Nov 2010 13:31:25 GMT

Think you should exchange the "$(i)" with "i" in your LET-statements, similarly in the FOR-statement you may simply refer to NoOfBases or replace straight with the formula.

HTH
Peter

Translate qv into sql code

Sun, 07 Nov 2010 16:58:38 GMT

Thank you, Peter. I replace FOR...NEXT loop with WHILE but the problem is with PEEK - as far as I know there's no such function in SQL except for TOP but this only lets choose the very top row(s) where as PEEK lets you choose any row.

Using TOP 1 you may create a script but you will have to delete the row after reading which I don't want to do and look for a better solution.

BR, Przemek