topic Flitering in RESIDENT tables in QlikView

Flitering in RESIDENT tables

Tue, 11 May 2010 12:44:42 GMT

Newbie query on loading data and then filtering.

I am trying to get the latest customer details using the Rank function.


CUSTOMER_ALL:
LOAD *;
SELECT Name, Product, Rank () (xxxx) as Rnk
FROM PROCUSTCUST;

CUSTOMER_LATEST:
LOAD *
RESIDENT CUSTOMER_ALL
WHERE Rnk = 1 ;

My problem is that when I select fields I cannot see the table CUSTOMER_LATEST. The CUSTOMER_ALL is always visible.
Please can someone inform where I have gone wrong.

Thanks in advance.

Flitering in RESIDENT tables

spsrk_84 — Tue, 11 May 2010 12:51:22 GMT

Hi,

Since the Fieldds names are common in both the tables i.e CUSTOMER_ALL and CUSTOMER_LATEST they might be concatenated, So after the resident table Drop the base table CUSTOMER_ALL

i.e

CUSTOMER_ALL:
LOAD *;
SELECT Name, Product, Rank () (xxxx) as Rnk
FROM PROCUSTCUST;

CUSTOMER_LATEST:
LOAD *
RESIDENT CUSTOMER_ALL
WHERE Rnk = 1 ;
DROP TABLE CUSTOMER_ALL

Try this i hope it will works

Regards

Ajay

Flitering in RESIDENT tables

Tue, 11 May 2010 12:52:06 GMT

The QlikView concatenate the tables with same name and number fields.

For don't concatenate, you have use NOCONCATENATE.


CUSTOMER_ALL:
LOAD *;
SELECT Name, Product, Rank () (xxxx) as Rnk
FROM PROCUSTCUST;

NOCONCATENATE
CUSTOMER_LATEST:
LOAD *
RESIDENT CUSTOMER_ALL
WHERE Rnk = 1 ;

Regards,

Tonial

Flitering in RESIDENT tables

Tue, 11 May 2010 12:54:01 GMT

Sorry to barge in on this one, Ajay is correct, but if you do want to have the CUSTOMER_ALL table available then you should rename the fields on the CUSTOMER_LATEST load for example:

CUSTOMER_LATEST:
LOAD Name as LATEST_Name, Product AS LATEST_Product, Rnk AS LATEST_Rnk
RESIDENT CUSTOMER_ALL
WHERE Rnk=1;

Cheers,

Flitering in RESIDENT tables

Tue, 11 May 2010 13:01:52 GMT

What exactly is

, Rank () (xxxx) as Rnk

doing? Had expected it would break the load statement.

Juerg

Flitering in RESIDENT tables

Tue, 11 May 2010 14:01:18 GMT

Hi Ajay,

Thanks for your prompt response.

I am using trail version of QlikView 9.

Ajay, My problem is that the output is not loaded into CUSTOMER_LATEST. The DROP statement drops CUSTOMER_ALL and I have no fields. Is this issue specific to trial version.

Tonial, the NOCONCATENATE CUSTOMER_LATEST: suggested helps but the data is not filtered. I get records where Rnk <> 1.

Juerg, Please cna you elaborate on "the NOCONCATENATE needs to be past the label to work"

Thanks in advance.

Flitering in RESIDENT tables

Tue, 11 May 2010 14:12:25 GMT

Expected the same to happen with Ajay's suggestion.

In Fernando's suggestion I think the NOCONCATENATE needs to be past the label to work (not before as in his code section shown).

You will be on the safe side with Nigel's approach but the correct Fernando solution should work too.

Regards

Juerg

Flitering in RESIDENT tables

Tue, 11 May 2010 14:51:37 GMT

Try this.


CUSTOMER_ALL:
LOAD *;
SELECT Name, Product, Rank () (xxxx) as Rnk
FROM PROCUSTCUST;

NOCONCATENATE
CUSTOMER_LATEST:
LOAD *
RESIDENT CUSTOMER_ALL
WHERE Rnk = 1 ;
DROP TABLE CUSTOMER_ALL;

This solution results only one table, the CUSTOMER_LATEST.

Regards.

Flitering in RESIDENT tables

Tue, 11 May 2010 15:07:17 GMT

Thanks for your help.

I renamed the column Rnk to Rank123 in CUSTOMER_LATEST and it has the desired affect. So

LOAD Name, Product, Rnk as Rank123 RESIDENT CUSTOMER_ALL works.