Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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));

View solution in original post

16 Replies
Miguel_Angel_Baeyens

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!

Not applicable
Author

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

JuanJo

Not applicable
Author

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

Miguel_Angel_Baeyens

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.

johnw
Champion III
Champion III

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));

Not applicable
Author

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

Not applicable
Author

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

Miguel_Angel_Baeyens

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.

Not applicable
Author

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