topic Iterate over table values and construct strings? in QlikView

Iterate over table values and construct strings?

Fri, 23 Apr 2010 20:56:43 GMT

I have a set of key values in a qlikview table:

keytable:
key1, key2, key3, ...

I want to iterate through these, construct SQL statements, and run the SQL statements and append the results into a new table.

for each key in keytable

LET SQL = "SELECT * FROM table WHERE key=", key, ";"
LOAD SQL;

Has anyone done something like this before? Any recommendations for how to do this?

Iterate over table values and construct strings?

johnw — Fri, 23 Apr 2010 21:14:08 GMT

Maybe something along these lines, but I'm rather unclear on the DETAILS of what you're trying to do... is "key1" a value or a field in keytable? Is ", key, " an actual value in your source table, or am I supposed to be substituting something there? Sure doesn't look like a value, but no idea what I'd be substituting. Blah blah blah other questions blah blah blah.

[New Table]:
LOAD *; SQL SELECT * FROM table WHERE $(=peek('key',I,'keytable')) = ", key, ";

FOR I = 0 TO noofrows('keytable')-1
CONCATENATE ([New Table])
LOAD *; SQL SELECT * FROM table WHERE $(=peek('key',I,'keytable')) = ", key, ";
NEXT I

Iterate over table values and construct strings?

Fri, 23 Apr 2010 22:13:47 GMT

Thanks John. That's perfect!

Now I don't suppose you would be able to show me how you could do this with a try...catch equivalent around the SELECT block? In some cases, I get an error message from SELECT statement, and I just want to skip those.

I read through this question, but it isn't clear to me:

http://community.qlik.com/forums/p/16288/63539.aspx

Iterate over table values and construct strings?

johnw — Fri, 23 Apr 2010 22:26:39 GMT

Sorry, I haven't played with the error mode, so I'd just be reading other forum posts to figure it out too. 🙂