topic Re: How to execute the query against the In Memory Data in QlikView

How to execute the query against the In Memory Data

Mon, 03 Sep 2012 12:29:25 GMT

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

Re: How to execute the query against the In Memory Data

whiteline — Mon, 03 Sep 2012 12:39:23 GMT

LOAD

...

Resident ...;

Re: How to execute the query against the In Memory Data

chematos — Mon, 03 Sep 2012 17:35:51 GMT

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

Re: How to execute the query against the In Memory Data

Tue, 04 Sep 2012 04:38:21 GMT

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

Re: How to execute the query against the In Memory Data

whiteline — Tue, 04 Sep 2012 05:23:57 GMT

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

Re: How to execute the query against the In Memory Data

Tue, 04 Sep 2012 05:51:03 GMT

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

Re: How to execute the query against the In Memory Data

whiteline — Tue, 04 Sep 2012 06:11:08 GMT

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;