topic How to remove fields values ? in QlikView

How to remove fields values ?

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

Hi All,

I have a query,

My table:

load * inline [

product,no

Sa,1

Sa-na,2

sa.no,3

no.sa,4

];

here I want to load only first data that is [Sa,1].i want to eliminate the rest from loading.

I was using where not match(product,' -na',' .no',' no') but it is not working.

I cannot use wildmatch because my data is case sensitive.

Any other function please help me:)

My result should be:

product,no

sa,1

Re: How to remove fields values ?

rubenmarin — Wed, 04 Apr 2018 08:48:56 GMT

Hi prashanthi, you can use Index() or SubstringCount()

https://help.qlik.com/es-ES/qlikview/November2017/Subsystems/Client/Content/Scripting/StringFunctions/Index.htm‌

https://help.qlik.com/es-ES/qlikview/November2017/Subsystems/Client/Content/Scripting/StringFunctions/SubStringCount.htm

Note that 'no' also includes '.no', you can get many false positives is those are similar to the real data you wil use.

Re: How to remove fields values ?

niclaz79 — Wed, 04 Apr 2018 08:49:11 GMT

Where product = 'Sa';

Or do you need smarter logic than that?

Re: How to remove fields values ?

vongalaprashant — Wed, 04 Apr 2018 08:53:29 GMT

Hi Niclas,

There are so many field values in my data.I can not use your logic.

Can you help me .

Thanks ,

prashanthi

Re: How to remove fields values ?

chinnuchinni — Wed, 04 Apr 2018 08:56:16 GMT

try this :

if you want only 1st row you can use this below script.

abc:

First 1 load * inline [

product,no

Sa,1

Sa-na,2

sa.no,3

no.sa,4

];

EXIT SCRIPT;

if you want to display only left 2 chars from Product you can use below script:

abc:

load * inline [

product,no

Sa,1

Sa-na,2

sa.no,3

no.sa,4

];

abc1:

load Left(product,2) as product1, no Resident abc;

DROP Table abc ;

EXIT SCRIPT ;

Re: How to remove fields values ?

niclaz79 — Wed, 04 Apr 2018 08:56:29 GMT

It gives you the expected result given the data you provided, can you clarify more what is the data you are using and what is the expected outcome.

Niclas Anderström

Mindcamp AB

niclas.anderstrom@mindcamp.se<mailto:niclas.anderstrom@mindcamp.se>

+46 702 39 21 43

Från: prashanthi reddy <qcwebmaster@qlikview.com>

Skickat: den 4 april 2018 10:54

Till: Niclas Anderström <niclas.anderstrom@mindcamp.se>

Ämne: Re: - How to remove fields values ?

Re: How to remove fields values ?

sasiparupudi1 — Wed, 04 Apr 2018 08:58:19 GMT

May be like

where SubstringCount(product,' -na',' .no',' no')=0

where (SubstringCount(product,'-na')=0 OR SubstringCount(product,'.no')=0 OR SubstringCount(product,' no')=0 )