topic Re: How to Drop field values while loading script in QlikView

How to Drop field values while loading script

nihhalmca — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

How to Drop field values while loading script

Ex: Field has values like A,B,C,D,E,F.....Z, i want to drop filelds 'C','D','E' from the field without using of WHERE/WHERE exists condition.

Thanks,

Nihhal.

Re: How to Drop field values while loading script

Anonymous — Tue, 29 Nov 2016 14:28:54 GMT

any specific reason for not using where/ where exists?

Re: How to Drop field values while loading script

Anonymous — Tue, 29 Nov 2016 14:29:39 GMT

hi.,

just comment the unnecessary fields....

then they don't come to front end.

Re: How to Drop field values while loading script

Anonymous — Tue, 29 Nov 2016 14:32:13 GMT

Is there any reason for not using any functions..

Re: How to Drop field values while loading script

marcus_sommer — Tue, 29 Nov 2016 14:40:05 GMT

You could also use a keep/join to filter a table like:

load KEY, further fields ... from MainTable;

inner join

load KEY from FilterTable;

But what speaks against using a where-condition?

- Marcus

Re: How to Drop field values while loading script

vishsaggi — Tue, 29 Nov 2016 15:36:20 GMT

Use a preceding load with an If but any links to those deleted values will have Nulls. For Example:

Value column 3, 4,5 values will have Nulls in their respective Name field.

LOAD Col1,

IF(Not Match(Name, 'C', 'D', 'E'), Name) AS NameFiltered;

LOAD * INLINE [

Value, Name

1, A

2, B

3, C

4, D

5, E

6, F

];

Re: How to Drop field values while loading script

rupamjyotidas — Tue, 29 Nov 2016 16:09:02 GMT

in script

Drop fields A,B from <Yourtablename>;

Re: How to Drop field values while loading script

vishsaggi — Tue, 29 Nov 2016 16:16:27 GMT

Hello Das,

I believe what Nihhal meant here is Field values rather than Fields. It might be typo!

"Ex: Field has values like A,B,C,D,E,F.....Z, i want to drop filelds (should have been drop values) 'C','D','E' from the field without using of WHERE/WHERE exists condition."

Re: How to Drop field values while loading script

rupamjyotidas — Tue, 29 Nov 2016 16:40:38 GMT

Oops. Main question would be why not use Where clause. Any reason for not using it??

Another solution

Table:

Load distinct

IF(Not Match(Name, 'C', 'D', 'E'), Name) AS NameFiltered;

From yourtablename

left Join

Load *

Name as NameFiltered

from yourtablename;

Re: How to Drop field values while loading script

bkn — Tue, 29 Nov 2016 17:02:12 GMT

Use PURGECHAR

Syntax:

PurgeChar(text, remove_chars)

Re: How to Drop field values while loading script

nihhalmca — Wed, 30 Nov 2016 07:53:53 GMT

If field has values [Product],[Product Type]...Purgechar remove both "Product".

Re: How to Drop field values while loading script

Anonymous — Wed, 30 Nov 2016 08:20:24 GMT

Hi,

I have few questions:

Any specific reason for not using where/ where exists?
Is it you want the records for 'C', 'D', 'E', but want to replace value 'C', 'D', 'E' with some other value?