topic Re: Using fields from one query as input to another in QlikView

Using fields from one query as input to another

marcusbohman — Wed, 25 Nov 2020 16:16:04 GMT

I have one table with two fields, e.g. ID and Name and another with e.g. ID and Salary. I want to be able to input a name and get the salary. My attempt as below.

LOAD "Emp_ID" as "ID";

IDandName:
SQL SELECT "Emp_ID",
"Name"
FROM "Table1" Where "Name" = 'MarcusBohman';

LET vID = peek("ID",0,'IDandName');

IDandSalary:
SQL SELECT "ID",
"Salary"
FROM "Table2" Where "ID" = $(vID);

I get error SQL##f - SqlState: 42000, ErrorCode: 27

Any suggestions?

Re: Using fields from one query as input to another

OmarBenSalem — Thu, 23 Nov 2017 20:56:47 GMT

Try as follow:

LOAD "Emp_ID" as ID;

IDandName:
SQL SELECT "Emp_ID",
"Name"
FROM "Table1" Where "Name" = 'MarcusBohman';

LET vID = peek('ID',0,'IDandName');

IDandSalary:

load * Where "ID" = $(vID); //or load * Where "ID" = '$(vID)';
SQL SELECT "ID",
"Salary"
FROM "Table2" ;

Re: Using fields from one query as input to another

marcusbohman — Fri, 24 Nov 2017 06:51:05 GMT

Thank you, That solved the error. However, I guess that I need to read the full Table2 and then only load the rows with the specific ID. Table2 has 2 billion+ rows and I don't want to read all rows. I stopped the proposed query after 5 minutes since I expect it to take a very long time to execute.

If I run the query with static values it takes less than 1 second.

Have I understood it correctly here? Is there a way to only select rows from the field value from another select?

/Marcus

Re: Using fields from one query as input to another

antoniotiman — Fri, 24 Nov 2017 08:31:19 GMT

Have You checked value of vID ?

Re: Using fields from one query as input to another

OmarBenSalem — Fri, 24 Nov 2017 08:39:33 GMT

then try :

LOAD "Emp_ID" as ID;

IDandName:
SQL SELECT "Emp_ID",
"Name"
FROM "Table1" Where "Name" = 'MarcusBohman';

LET vID = peek('ID',0,'IDandName');

IDandSalary:

l
SQL SELECT "ID",
"Salary"
FROM "Table2" load * Where "ID" = '$(vID)'; // or Where "ID" = $(vID);

Re: Using fields from one query as input to another

marcusbohman — Fri, 24 Nov 2017 08:43:30 GMT

Was not sure how to do it but I think I was able to find through running steps in debugging. It is Null for both the variable and "ID" after debug LET vID... Stepped through all other lines before

Re: Using fields from one query as input to another

marcusbohman — Fri, 24 Nov 2017 08:50:13 GMT

omar bensalem skrev:

then try :

LOAD "Emp_ID" as ID;

IDandName:
SQL SELECT "Emp_ID",
"Name"
FROM "Table1" Where "Name" = 'MarcusBohman';

LET vID = peek('ID',0,'IDandName');

IDandSalary:

l
SQL SELECT "ID",
"Salary"
FROM "Table2" load * Where "ID" = '$(vID)'; // or Where "ID" = $(vID);

omarbensalem Gives the same error as in my initial query. SQL##f - SqlState: 42000, ErrorCode: 27

Re: Using fields from one query as input to another

marcusbohman — Mon, 27 Nov 2017 14:36:37 GMT

marcusbohman skrev:

I have one table with two fields, e.g. ID and Name and another with e.g. ID and Salary. I want to be able to input a name and get the salary. My attempt as below.

LOAD "Emp_ID" as "ID";

IDandName:
SQL SELECT "Emp_ID",
"Name"
FROM "Table1" Where "Name" = 'MarcusBohman';

LET vID = peek("ID",0,'IDandName');

IDandSalary:
SQL SELECT "ID",
"Salary"
FROM "Table2" Where "ID" = $(vID);

I get error SQL##f - SqlState: 42000, ErrorCode: 27

Any suggestions?

I found the issue myself. Peek expects single quotes (') instead of double quotes ("). I changed to LET vID = peek('ID',0,'IDandName'); and it worked fine. Thank you all for spending time to help me.

Re: Using fields from one query as input to another

OmarBenSalem — Mon, 27 Nov 2017 14:41:15 GMT

I changed that for you sicne the 24th of Nov

Re: Using fields from one query as input to another

marcusbohman — Mon, 27 Nov 2017 15:01:20 GMT

omar bensalem skrev:

I changed that for you sicne the 24th of Nov

I am sorry omarbensalem‌, I read your answer to sloppy. I will set your answer as the correct one.