topic Re: How to filter columns? in QlikView

How to filter columns?

Mon, 14 Jul 2014 14:58:19 GMT

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?

Re: How to filter columns?

PrashantSangle — Mon, 14 Jul 2014 15:00:45 GMT

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,

Re: How to filter columns?

Mon, 14 Jul 2014 15:17:48 GMT

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?

PrashantSangle — Mon, 14 Jul 2014 15:20:35 GMT

Hi,

Can you explain with examples??

Regards,

Re: How to filter columns?

Mon, 14 Jul 2014 15:25:56 GMT

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

CustomerID	Name	City	State	Zip	Country
111	xxx	xx	XX	11111	USA
222	yyy	yy	YY	22222	USA

output I want is

CustomerID	City	Country
111	xx	USA
222	yy	USA

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?

alexandros17 — Mon, 14 Jul 2014 15:57:31 GMT

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

YYY:

NoConcatenate

LOAD $(LLL) Resident XXX;

DROP Table XXX;

Re: How to filter columns?

erivera10 — Mon, 14 Jul 2014 17:41:05 GMT

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).