Expression

[#1]

Hi all,

I have 2 columns. ID and Value like below.

ID         Value

1245       10

1569        8

2145       15

2356       20

Sales = count of the "Value" that anything starts with number "1" in ID column.

Costs = count of the "Value" that anything starts with number "2" in ID column.

Can someone tell me how to write the expression.

Thanks

[avinashelite]

try like this

sales :  if(ID like '1*',count(Value))

cost:  if(ID like '2*',count(Value))

[qlikmsg4u · Accepted solution]

=If(Left(ID,1)=1,Count(Value),Count(Value))

or

Sales : =If(Left(ID,1)=1,Count(Value))

Costs : =If(Left(ID,1)=2,Count(Value))

[maxgro]

Sales = count of the "Value" that anything starts with number "1" in ID column.

Costs = count of the "Value" that anything starts with number "2" in ID column.

count({$ <ID={"1*"}>} Value)

[Kushal_Chawda]

try

Sales: count({<ID ={"1*"}>}Value)

Costs: count({<ID ={"2*"}>}Value)

[MayilVahanan]

Hi

Try like this

Value:

LOAD *, Pick(WildMatch(ID, '1*', '2*'), 1, 2,3) AS Type INLINE [

    ID,Value    

    1245,10

    1569, 8

    2145,15

    2356,20

];

Sales =Sum({<Type = {1}>}Value)

Cost  =Sum({<Type = {2}>}Value)

[#7]

Thanks to both of you. Both expressions working fine.

[#8]

Thanks everyone.

[avinashelite]

if you have got the answer please mark this thread as answer and close this thread ..so that it will be helpful for others

[qlikviewwizard]

One question, different answers. Nice.