How to execute the query against the In Memory Data

[#1]

Hi

I have one QVW (order.qvw) in which I have loaded following 3 sample tables

Order mst

order_id & Order_dt

1	8/1/2012
2	8/16/2012

Order Detail

Oorder_id Prod_NM, Quantity, Unit_Price

1	Stationery	50	16
1	Pencils	15	4
2	School Bag	25	150
2	Water Bag	25	70

SQL_QUERY

Order_id, SQL_QUERY

1	SELECT order_dtl.order_id,Sum(Order_Dtl.Unit_Price * Order_Dtl.Quantity) as `Result` FROM order_dtl, order_mst where order_mst.order_id = Order_Dtl.order_id group by Order_Dtl.order_id

I have another QVW (ORDER_1.qvw) which I have inherited from above i.e. (order.qvw).

Now I want to execute the Query (SELECT order_dtl.order_id,Sum(Order_Dtl.Unit_Price * Order_Dtl.Quantity)……….) of table ‘order_query’ and store the result in ORDER_1.qvw.

Please note I do NOT want to execute this query in DB instead I want to execute against the In Memory Data.

Please do let me know how to do it?

Regards

Nilesh

[whiteline]

LOAD

...

Resident ...;

[chematos]

If you want to use the same QUERY in different qvw's, you should store the result of the query in QVD files, so you will read the QVD files in multiple qvw's if you want.

So, after doing the SELECT, you must store in QVD like this:

TABLE:

Load *

;

ELECT order_dtl.order_id,Sum(Order_Dtl.Unit_Price * Order_Dtl.Quantity) as `Result`

FROM order_dtl, order_mst

where order_mst.order_id = Order_Dtl.order_id

group by Order_Dtl.order_id

;

Store * from TABLE into TABLE.QVD;

So, this table is in a QVD file and you can read using the File... button in your script.

If you want to read data of one table in the same qvw you must use a resident table, like this:

TABLE:

Load *

;

ELECT order_dtl.order_id,Sum(Order_Dtl.Unit_Price * Order_Dtl.Quantity) as `Result`

FROM order_dtl, order_mst

where order_mst.order_id = Order_Dtl.order_id

group by Order_Dtl.order_id

;

TABLE1:

NoConcatenate

Load *

Resident TABLE;

Hope this helps,

Regards

[#4]

Thanks Jose!

In both the cases SELECT query is going to hit the underlying DB. I want to execute Select query in second QVW where i do not have any ODBC connection. Also I want to loop thru the Order query to fetch the Select query and execute it against the In-Memory data.

Regards

Nilesh

[whiteline]

In case you want to load all the data from one qvw to another you can use Binary statement.

[#6]

I am doing so. As I mentioned in my 1st post -

I have another QVW (ORDER_1.qvw) which I have inherited from above i.e. (order.qvw).

[whiteline]

As you load all date from the first one to the second one with binary you have all your tables in your second file.

So If you want your 'result' in script you can use LOAD Resident as I said above.

In case you have some troubles with transforming your SQL to QV LOADs:

In QlikView you can't query from more than one table. So in general case you need to create another table from the first one and join the second one. Then you can group.

In your case an easier solution exists with keep statement.

SQL_QUERY:

left keep([Order mst])

LOAD

     order_id,

     Sum(Order_Dtl.Unit_Price * Order_Dtl.Quantity) as Result

FROM [Order Detail]

group by order_id;