topic Re. :How select data from other TableBox? in QlikView

How select data from other TableBox?

Fri, 14 May 2010 13:39:51 GMT

I need show 2 TableBox on one Sheet.
1st TableBox (SelectingAllData) consists ALL data from one table.
2nd TableBox (SelectingDataByParams) will be include part data of 1st TableBox.
I would like implement changes for 2nd TableBox without full Reload data.
Help me plz.


SelectingAllData:
SQL SELECT fld1, fld2, fld3, fld4 FROM tblData;
// code below is wrong and it is my question
SelectingDataByParams:
SQL SELECT fld1, fld2
FROM SelectingAllData
WHERE fld3 >= $(Var01) and fld4 = $(Var02);

Re. :How select data from other TableBox?

martin59 — Fri, 14 May 2010 13:49:43 GMT

Hello,

You have to use the RESIDENT syntax :

<pre>SelectingDataByParams:
SQL SELECT fld1, fld2
RESIDENT SelectingAllData
WHERE fld3 >= $(Var01) and fld4 = $(Var02);

I hope that helps you
Martin

Re. :How select data from other TableBox?

Fri, 14 May 2010 15:09:13 GMT

This code raises error during Reload


Martin Favier wrote:
You have to use the RESIDENT syntax :
SelectingDataByParams:
SQL SELECT fld1, fld2
RESIDENT SelectingAllData
WHERE fld3 >= $(Var01) and fld4 = $(Var02);

But this my code Reloaded successfully


SelectingDataByParams:
LOAD *
RESIDENT SelectingAllData
WHERE fld3 >= $(Var01) and fld4 = $(Var02);

BUT I have got following message after Reload

SelectingAllData << tblData 1 591 833 lines fetched
SelectingAllData << SelectingAllData 3 183 666 lines fetched

and NO tables "SelectingDataByParams"

Why?

Re. :Re: Re. :How select data from other TableBox?

martin59 — Fri, 14 May 2010 15:21:14 GMT

Sorry I had'nt take care to the SQL SELECT !

You load 2 tables strictly identical (same number of fields and same name of fields), QlikView see that and concatenate both.

So you can do this :

<pre>SelectingDataByParams:
NOCONCATENATE LOAD *
RESIDENT SelectingAllData
WHERE fld3 >= $(Var01) and fld4 = $(Var02);


SelectingDataByParams:
LOAD fld1, fld2
RESIDENT SelectingAllData
WHERE fld3 >= $(Var01) and fld4 = $(Var02);

But take care, you will have some synthetic keys.
Martin

Re. :Re: Re. :How select data from other TableBox?

Fri, 14 May 2010 21:33:39 GMT


Martin Favier wrote:Sorry I had'nt take care to the SQL SELECT
.....
But take care, you will have some synthetic keys.
Martin

Thanks Martin!

I will try your code.

But what about dynamic refresh TableBox2 (with SelectingDataByParams) when I will change variables Var01 and/or Var02?

Will automaticlly reload data in TableBox2 (with SelectingDataByParams) or I need to do something (run macro etc.)?