topic Howto use field-value previously charged in another sql statement in script in QlikView

Howto use field-value previously charged in another sql statement in script

Tue, 05 Oct 2010 11:25:18 GMT

I want to use the value of a field previously charged from a table by sql, in the next sql-statement. such as

Table1:

select CodeId, date from outTable1 where date = x;

table2:

select CodeId, other from outTable2 ...

and now what I want, use the value of previous Table1.CodeId as selection for table2, sucha as

where outTable2.CodeId in (select, load, exist wathever Table1.CodeId));

I don´t know if I make myself understod.

Supposedly Table1 is now resident, so I should be able to read Table1.CodeId to compare in teh script.

Haven´t found solution.

The hint is that outTable2 doesn´t have Date-field, I have to use the key-field CodeId to make an incremental load, of just new records on outTable1 and outTable2

Thanks

JuanJo

Howto use field-value previously charged in another sql statement in script

Miguel_Angel_Baeyens — Tue, 05 Oct 2010 11:43:50 GMT

Hello Juanjo,

You can do that using exists()

Table1:LOAD CodeId, date;SQL Select CodeId, date from outTable1 where date = x; table2:LOAD *WHERE EXISTS(CodeId);SQL Select CodeId, other from outTable2 ...

Hope that helps!

Howto use field-value previously charged in another sql statement in script

Tue, 05 Oct 2010 12:50:13 GMT

I'm give it a try, but before I do. Never seen first load and after select, Thanks, I´ll tell you

JuanJo

Howto use field-value previously charged in another sql statement in script

Tue, 05 Oct 2010 13:24:19 GMT

Now, it´s working well, a little slow, but no so much as charging full outTable2.

I have to use

Table1:

load * ;

SQL Select ....

Table2:

Load * where exists(CodeId);

SQL Select ....

If I put fields in Load, doesn´t work, crashes.

Thank you very much, one more step to total "INCREMENTAL" load in my doings.

JuanJo

Howto use field-value previously charged in another sql statement in script

Miguel_Angel_Baeyens — Tue, 05 Oct 2010 16:45:17 GMT

For what it's worth, LOAD field names before renaming (if any) must match with those retrieved from database, meaning they are case sensitive, meaning is you do

select CodeId, date from table;

You must load

LOAD CodeId, date;

Apart from that, I always do this way as I control any transformation I need to do for QlikView applications and this way I only load those fields I need, regardless the information I'm pulling from the database.

Regards.

Howto use field-value previously charged in another sql statement in script

johnw — Tue, 05 Oct 2010 17:04:36 GMT

Two approaches might make the load more efficient. First, you can just do the table2 load as a join in the SQL:

SQL SELECT 2.CodeId, 2.other
FROM Table1 1, Table2 2
WHERE 1.date = x
AND 2.CodeId = 1.CodeId;

Second, you can convert the list of CodeIds from the first table into a variable, and use the variable in the second select. Subject to likely syntax errors, something like this:

Temp:
LOAD concat(CodeId,chr(39)&','&chr(39)) as Codes
RESIDENT Table1;
LET vCodes = chr(39) & peek('Codes') & chr(39); // fixed per later posts
DROP TABLE Temp;
Table2:
LOAD *;
SQL SELECT CodeId, other
FROM Table2
WHERE CodeId IN ($(vCodes));

Howto use field-value previously charged in another sql statement in script

Wed, 06 Oct 2010 08:51:48 GMT

Thank you very much John,

First idea works fine and fast, really fast. Used to solve and charge one by one, I hadn´t visualize doing it so.

The second idea doesn´t do what we expect, only one register read from resident Table1, and then goes to table2 sql and reads the whole table, which shoudn´t in any case, just what's inside $vCodes.

I wonder what got inside vCodes... ????

Anyway the first solution works fine.

Thanks very much

JuanJo

Howto use field-value previously charged in another sql statement in script

Wed, 06 Oct 2010 09:33:05 GMT

I have a hint of what happens,

The chain Codes laks of a ' " ' sign at the very beginning, I have let the full work be done, and after loading whole table2, shows me the error or warning CodeId IN (alue1","value2"...) "unknown sentence".

Anyway it shouldn't load, and crash at the IN sentence. But vCodes get all values right, less teh " sign at the begining.

I don't like unsolved problems, so that's why I'm digging on it. The first solution works fine, but I like the second as elegant, and if it worked, by solving the problem, I'd had one more tool.

Always learning

JuanJo

Howto use field-value previously charged in another sql statement in script

Miguel_Angel_Baeyens — Wed, 06 Oct 2010 10:33:51 GMT

Helo Juanjo,

Just taking John's syntax one step further,

Temp:LOAD concat(CodeId,chr(39)&','&chr(39)) as CodesRESIDENT Table1;LET vCodes = chr(39) & peek('Codes') & chr(39);

Now, vCodes wil get all values properly quoted.

Regards.

Howto use field-value previously charged in another sql statement in script

Wed, 06 Oct 2010 12:40:26 GMT

I've just tested, and makes the same error, but vCodes is right with its slashes, now yes. But still doesn't work and keeps loading the whole table. Here I would say an improper word. 🙂

strange, should work. Does the in statement have a limit to the size of the list? Does the capacity of a variable has any limitation? Maybe it fails to read the whole variable values.

Again, the problem now is solved with the combined reading of the two tables as A and B, and is fast. I just don't like living things unsolved. green

JuanJo

Howto use field-value previously charged in another sql statement in script

Miguel_Angel_Baeyens — Wed, 06 Oct 2010 12:59:49 GMT

Hi,


aJuanJoq wrote:strange, should work. Does the in statement have a limit to the size of the list? Does the capacity of a variable has any limitation? Maybe it fails to read the whole variable values.

It depends on your database and odbc driver, but it's likely to be some limitation on the parameters that can be passed in one sentece. Another option would be to set the IN clause in the LOAD part instead of the SQL part. Untested though, it should look like

Table2:LOAD * WHERE MATCH(CodeId, $(vCodes)) > 0;SQL SELECT *FROM Table;

I don't think either this as a good solution in terms of performance.

Regards.

Howto use field-value previously charged in another sql statement in script

Wed, 06 Oct 2010 13:53:30 GMT

First of all I want to beg your perdon. I had a semicolon after the table and before the where statement of the WHERE IN. Sorry.

But anyway it doesn´t work, now it crashes and doesn't load the whole table which is right.

It may be, not working, because cannot read the whole variable vCodes, when I receive the error, shows me lots of IN('value1','value2','value3'... but not the whole variable value, truncated.

I'm using TransTools engine

JuanJo

Howto use field-value previously charged in another sql statement in script

Wed, 06 Oct 2010 13:54:51 GMT

and Miguel, your last offer doesn't work. freezes

Howto use field-value previously charged in another sql statement in script

johnw — Wed, 06 Oct 2010 20:58:56 GMT

Your guess that it isn't passing the whole thing to the database sounds reasonable. In that case, there's no way around it that I'm thinking of. Miguel's version moving the match to the load will just reintroduce the performance (as he suggests), since you're now reading the whole table again, and only throwing out rows after retrieving them to QlikView. Not sure why it isn't working at all, though.

So perhaps the best option for this specific case is the one you already have working now, with the join in SQL. Just keep the other one in mind for future cases where the list you need to pass is shorter. How short is an open question.

Howto use field-value previously charged in another sql statement in script

Thu, 07 Oct 2010 07:42:07 GMT


John Witherspoon wrote:
Your guess that it isn't passing the whole thing to the database sounds reasonable. In that case, there's no way around it that I'm thinking of. Miguel's version moving the match to the load will just reintroduce the performance (as he suggests), since you're now reading the whole table again, and only throwing out rows after retrieving them to QlikView. Not sure why it isn't working at all, though.
So perhaps the best option for this specific case is the one you already have working now, with the join in SQL. Just keep the other one in mind for future cases where the list you need to pass is shorter. How short is an open question. <div></div>

Thanks John, and all others too. 🙂

Just want to mention two things. First, as I said above, I had a semicolon, after from table and before where field in($(vCodes), sorry, that's why it was rezadin the whole table, instead of crashing, after that was the error in reading the sentence where field in (...). Anyway, it doesn´t work, once corrected the missmatch.

second, with Miguel's version, all the chr(39) get in place right. But again it doesn't work.

The reason could be that of not being able to read the whole variable. But, sure I will keep this solution in mind in order to try it in other circumstances.

Thanks

JuanJo

Howto use field-value previously charged in another sql statement in script

Fri, 08 Oct 2010 13:26:08 GMT

Finally, I got it working, as I was fighting with a variable date, that looked like right but didn´t work. I have found the solution, or why it didn´t work, with the sentence IN....

It was the caracter used to delimiter the string to be compared, I must use chr(034)" doble and not chr(039)' simple, because of my database sql.

I have remade the peek('Codes') solution and works just fine, finally..

Thanks

JuanJo

Howto use field-value previously charged in another sql statement in script

Fri, 08 Oct 2010 13:38:17 GMT

I have done the test with less than one day loading, no more tan 20 registers, up to 100 works fine, upper fails to read...