topic PLS GIVE EXPRESSION FOR THIS in QlikView

PLS GIVE EXPRESSION FOR THIS

Thu, 15 May 2014 17:45:07 GMT

(Vendor Invoice Value x 2.50)/100. This formula should be applied for the only following Storage Locations -IB01, IB04, IF01, IF02, IF04, IL01, IL02, IN01, IN02, IN04, ING1, IP01, IP04

PLS TELL ME EXPRESSION FOR THIS

Re: PLS GIVE EXPRESSION FOR THIS

m_woolf — Fri, 16 May 2014 11:34:51 GMT

if(index(StorageLocation,'IB01, IB04, IF01, IF02, IF04, IL01, IL02, IN01, IN02, IN04, ING1, IP01, IP04')>0,

(Vendor Invoice Value x 2.50)/100,

Vendor Invoice Value) as Whatever;

Re: PLS GIVE EXPRESSION FOR THIS

Wed, 21 May 2014 05:15:40 GMT

what is index here

Re: PLS GIVE EXPRESSION FOR THIS

tresB — Wed, 21 May 2014 05:31:33 GMT

Using Match(), like:

=Sum(If(Match(StorageLocation,'IB01', 'IB04','IF01', 'IF02', 'IF04', 'IL01', 'IL02', 'IN01', 'IN02', 'IN04', 'ING1', 'IP01', 'IP04') , Vendor [Invoice Value x 2.50] )/100)

Re: PLS GIVE EXPRESSION FOR THIS

anbu1984 — Wed, 21 May 2014 05:31:41 GMT

Index is string function

From QV help

index(s1 , s2[ , n])

Position of a substring. This function gives the starting position of the n:th occurrence of substring s2 in string s1. If n is omitted, the first occurrence is assumed. If n is negative, the search is made starting from the end of string s1. The result is an integer. The positions in the string are numbered from 1 and up.

Your field StorageLocation should be second argument to Index function like below

if(index('IB01, IB04, IF01, IF02, IF04, IL01, IL02, IN01, IN02, IN04, ING1, IP01, IP04',StorageLocation)>0,

(Vendor Invoice Value x 2.50)/100,

Vendor Invoice Value) as Whatever;

Re: PLS GIVE EXPRESSION FOR THIS

anbu1984 — Wed, 21 May 2014 05:34:33 GMT

Index can be used like this

index('IB01, IB04, IF01, IF02, IF04, IL01, IL02, IN01, IN02, IN04, ING1, IP01, IP04',StorageLocation)

Re: PLS GIVE EXPRESSION FOR THIS

MayilVahanan — Wed, 21 May 2014 05:57:01 GMT

Try with Match function

=Sum(If(Match(StorageLocation,'IB01', 'IB04','IF01', 'IF02', 'IF04', 'IL01', 'IL02', 'IN01', 'IN02', 'IN04', 'ING1', 'IP01', 'IP04') ,

(Vendor [Invoice Value x 2.50] )/100, 0))

hope it helps