topic Re: Load Where - Resident issue in QlikView

Load Where - Resident issue

Fri, 25 Apr 2014 09:18:57 GMT

I have a table loaded within a script that is populated with some data not required - I want to limit the data by using the resident command to load a new table with a subset of the data.

As an example the image below show a Entities and Entity ID's currently in the table - I want to reduce them to those with an [Entity ID] of less than 11

I tried the following , but it returns no values into the new table

D_ShowAllBr:

Load *

Resident D_ShowAllBrtmp

where Match([Entity ID],'1' ,'2' , '3', '4', '5', '6', '7', '8', '9', '10');

I have tried Wild Match and < 11 as an option .. each time I get an empty table

I am unable to use the SQL in the initial query to limit it due to an issue with the adapter returning the data ..

Any pointers please.

Drop Table D_ShowAllBrtmp;

Re: Load Where - Resident issue

MK_QSL — Fri, 25 Apr 2014 09:22:54 GMT

Try as below

D_ShowAllBr:

Load *

Resident D_ShowAllBrtmp

where [Entity ID] <= NUM(10);

Re: Load Where - Resident issue

Fri, 25 Apr 2014 09:28:22 GMT

Thanks Manish, but I have the same issue .. 0 returned values and no table created ..

As soon as I remove the where .. table creates with all values ..

Re: Load Where - Resident issue

rustyfishbones — Fri, 25 Apr 2014 09:29:55 GMT

you can try

D_ShowAllBr:

LOAD *

RESIDENT D_ShowAllBrtmp

where [Entity ID] <= 11;

DROP Table D_ShowAllBrtmp;

Re: Load Where - Resident issue

rbecher — Fri, 25 Apr 2014 09:32:36 GMT

You probably have to use

D_ShowAllBr:

NOCONCATENATE LOAD...

Re: Load Where - Resident issue

Fri, 25 Apr 2014 09:34:01 GMT

Thanks, same result. No table.

Re: Load Where - Resident issue

maxgro — Fri, 25 Apr 2014 09:35:47 GMT

does it returns no value or no table?

it seems you're missing a noconcatenate as you load *

EDIT:ops,I'mlate

Re: Load Where - Resident issue

Fri, 25 Apr 2014 09:35:54 GMT

Spot on and thanks ..

Re: Load Where - Resident issue

rustyfishbones — Fri, 25 Apr 2014 09:41:51 GMT

Why would he have to use NOCONCATENATE

If the Table is being Dropped?

Re: Load Where - Resident issue

rbecher — Fri, 25 Apr 2014 09:43:56 GMT

Because it's appending to the already existing table (same structure), which gets dropped at the end. So, no data loaded at all...

Re: Load Where - Resident issue

Fri, 25 Apr 2014 09:44:24 GMT

I would ask the same .. but placing NoConcatenate into the Load section seems to have done the trick .. not had to do it before ...

Re: Load Where - Resident issue

rustyfishbones — Fri, 25 Apr 2014 09:47:22 GMT

Hi Ralf,

So what if he aliased one of the Fields

so like

NewTable:

LOAD *,

Code as NewCode

RESIDENT Data

where Code <= 11;

DROP Table Data;

Then you would not need NOCONCATENATE, is that correct?

Re: Load Where - Resident issue

rbecher — Fri, 25 Apr 2014 09:49:38 GMT

What's wrong with NOCONCATENATE? It's the better way, I think..

Re: Load Where - Resident issue

rustyfishbones — Fri, 25 Apr 2014 09:52:53 GMT

nothing wrong with NOCONCATENATE

just trying to get a better understanding of how it works, that's all!

Re: Load Where - Resident issue

rbecher — Fri, 25 Apr 2014 09:55:18 GMT

Extra field would probably work but consumes more memory and time..

Re: Load Where - Resident issue

rustyfishbones — Fri, 25 Apr 2014 09:57:28 GMT

Excellent Ralf,

Thanks

Regards

Alan