Exclude values with a prifix

[kakani87]

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.

[olivierrobin]

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']>}

[olivierrobin]

try

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

and use the same logic

[YoussefBelloum]

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

[ahmar811 · Accepted solution]

Hi,

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

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

or

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

If you want to exclude using set analysis

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

Regards

Ahmar

[kakani87]

No

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

]

;

[kakani87]

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)

[kakani87]

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