Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

How to filter columns?

Hi All,

could anybody know how to apply filter conditions for field names?

Example: I have 100 fields in my table. I want to load only fields which start with letter A. How i can do this in qlikview?

1 Solution

Accepted Solutions

Re: How to filter columns?

Here is my example, let me know

XXX:

LOAD * Inline [

AA, AS, A9, BA, BS

1,2,3,4, 5

];

LET LLL = '';

FOR i=1 to NoOfFields('XXX')

  If Index(FieldName($(i),'XXX'),'A')=1 then

  IF $(i)=1 then

  LLL = FieldName($(i),'XXX');

  ELSE

  LLL = LLL & ',' & FieldName($(i),'XXX');

  ENDIF

  ENDIF

NEXT

YYY:

NoConcatenate

LOAD $(LLL) Resident XXX;

DROP Table XXX;

6 Replies

Re: How to filter columns?

Hi,

In Script

you can achieve this with Where Clause

Where wildmatch(fielname,'A*');

Or in dimension

if(wildmatch(fieldname,'a*'),Fieldname)

and then tick supress null values in dimension tab.

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Not applicable

Re: How to filter columns?

Thanks max. But I am not asking how to filter field values while loading. I want to filter fields itself.

Re: How to filter columns?

Hi,

Can you explain with examples??

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Not applicable

Re: How to filter columns?


Here is the example. From below table i want to load all the fields which starts with letter "C"

CustomerIDNameCityStateZipCountry
111xxxxxXX11111USA
222yyyyyYY22222USA

output I want is

CustomerIDCityCountry
111xxUSA
222yyUSA

I would like to put that filter condition in my script like

select *

from Customers

where fieldname starts with letter 'C'

Re: How to filter columns?

Here is my example, let me know

XXX:

LOAD * Inline [

AA, AS, A9, BA, BS

1,2,3,4, 5

];

LET LLL = '';

FOR i=1 to NoOfFields('XXX')

  If Index(FieldName($(i),'XXX'),'A')=1 then

  IF $(i)=1 then

  LLL = FieldName($(i),'XXX');

  ELSE

  LLL = LLL & ',' & FieldName($(i),'XXX');

  ENDIF

  ENDIF

NEXT

YYY:

NoConcatenate

LOAD $(LLL) Resident XXX;

DROP Table XXX;

erivera10
Contributor

Re: How to filter columns?

Sandeepdonthu Hello, I hope this works is through Set Analysis.

Account IdAgent call starting with J, ignoring IdAgent selection.

Count ({<IdAgent={"J*"}>} IdCall)

Total sales of products starting with letter A

sum ({<Products= {"A*"}>} Sales)

Add "$" to respect the selection, eg

Count ({$ <IdAgent={"J*"}>} IdCall).

Sum ({$ <Products= {"A*"}>} Sales).

Community Browser