topic How to use Query Result From One Query in Another? in QlikView

How to use Query Result From One Query in Another?

Wed, 18 May 2011 17:17:48 GMT

Suppose I have a Query Say "Select Ticket_ID, Status from Table A". It returns Ticket_ID values as 1, 2, 3

Now I want to use the values in Another query without writing a sub query

Query Should be Select ticket_ID, Sum(Value) from Table B Where Ticket_ID in (1, 2, 3) i.e.values from Query 1.

And Not Select Ticket_ID, sum(Value) from Table A Where Ticket_ID in (Select Ticket_ID from table A)

Because the actual Query I have Is quite big and I dont want to use that as a subquery.

I cannot used join here since it with give me 1 -> n values which will give incorrect results.

Please suggest

Regards

Suresh

How to use Query Result From One Query in Another?

chriscammers — Wed, 18 May 2011 17:49:11 GMT

I would suggest storing the Ticket_ID's from your first query in a variable, you would use the Concat() function to create a comma separated list and then pass the variable in your sql using syntax something like this...

Tickets:

Load

Concat(Chr(39) & Ticket_ID & Chr(39),', ') as Tickets;

Select Ticket_ID from...;

Let YourVar = Peek('Tickets',0,'Tickets');

Select * from Blah where Ticket_ID In ($(YourVar));

Re: How to use Query Result From One Query in Another?

Fri, 20 May 2011 09:06:13 GMT

Try this:

Test:

Load

Ticket_ID,

Status

from TableA.qvd (qvd);

concatenate

Load

ticket_ID as Ticket_ID, // I assume that ticket_ID is another field in table B

Value //which has same values as Ticket_ID from table A

from TableB.qvd (qvd);

Test1:

Load

Ticket_ID,

Value

resident Test where Ticket_ID=1 or

Ticket_ID=2 or Ticket_ID=3;

drop table Test;

Test2:

Load

Ticket_ID,

Sum(Value) as Total_Value

resident Test1 group by Ticket_ID;

drop table Test1;

Re: How to use Query Result From One Query in Another?

Fri, 20 May 2011 16:24:32 GMT

Try something like this:

TB_A:

SELECT
TICKET_ID,
STATUS
FROM TABLE_A;
INNER JOIN

SELECT
TICKET_ID,
SUM(VALUE) AS TICKET_VALUE
FROM TABLE_B
GROUP BY TICKET_ID;