Flitering in RESIDENT tables

[#1]

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.

[spsrk_84]

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

[#3]

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

[#4]

Hi

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,

[#5]

Hi

What exactly is

, Rank () (xxxx) as Rnk

doing? Had expected it would break the load statement.

Juerg

[#6]

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.

[#7]

Hi

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

[#8]

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.

[#9]

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.