topic Re: Exclude values with a prifix in QlikView

Exclude values with a prifix

kakani87 — Thu, 12 Apr 2018 08:20:01 GMT

Hi Folks .... i want to display only invoice numbers with prifix 'L' by excluding 'T' in a chart along with other fields

lets say inv and branch wise sale.

Sample data I'm posting here

Inv_Key:

load * inline [

Inv,Br,Sale

011/17/T/2,011,5000

011/17/T/2,011,100

012/17/L/3,012,500

013/17/L/2,013,1000

013/17/T/2,013,200

015/17/L/4,015,100

]

;

Regards,

Kishore.

Re: Exclude values with a prifix

olivierrobin — Thu, 12 Apr 2018 08:24:55 GMT

hello

do you want to exclude them in the load ?

so add where left(Inv,1)='L'

or where left(Inv,1)<>'T'

if you want to include/exclude in a chart, use set analysis

and in this case, in your load, add a column prefix

left(Inv,1) as Prefix

so that you can use it easily in set analysis in this way

{$<Prefix={'L']>}

or {$<Prefix-={'T']>}

Re: Exclude values with a prifix

olivierrobin — Thu, 12 Apr 2018 08:33:00 GMT

try

subfield(Inv,'/',3) as prefix (IF it is always the 3rd field delimited by /)

and use the same logic

Re: Exclude values with a prifix

YoussefBelloum — Thu, 12 Apr 2018 08:38:07 GMT

Hi,

If you don't want or you can't modify the script, here is one set analysis excluding all Inv with T prefix:

{<Inv={"=not WildMatch(Inv,'*T*')"}>}

You can use with any aggregation function

Re: Exclude values with a prifix

ahmar811 — Thu, 12 Apr 2018 08:38:44 GMT

Hi,

If you want to exclude in load just add below expression in where

where wildmatch(Inv,'*T*')=0

where not wildmatch(Inv,'*T*')

If you want to exclude using set analysis

{<Inv-={*T*}>}

Regards

Ahmar

Re: Exclude values with a prifix

kakani87 — Thu, 12 Apr 2018 08:40:59 GMT

i want to display in chart only

No prifix field considered to use set expression to exclude

lets say 'L' is return value and 'T' is sale value

in the sample posted earlier data little correction

Inv_Key:

load * inline [

Inv,Br,Sale

011/17/T/2,011,5000

011/17/L/2,011,100

012/17/T/3,012,3000

012/17/L/3,012,500

013/17/L/2,013,1000

013/17/T/2,013,200

015/17/T/4,015,1000

015/17/L/4,015,100

]

;

Re: Exclude values with a prifix

kakani87 — Thu, 12 Apr 2018 08:48:03 GMT

Thanks a lot Ansari this worked by adding ' ' to your expression.

i was trying something like this ={'T*'} and ={"T*"}

This served my purpose.

=sum({<sales_pk-={'*T*'}>}sales)

Re: Exclude values with a prifix

kakani87 — Thu, 12 Apr 2018 12:28:29 GMT

I appreciate Olivier Robin and Youssef Belloum for responding to my post