Operator "in" on Qlik View

[#1]

Hi everyone;

It embarrases my but how i can do to work de operator "IN" from SQL on QlikView?  i have the next expression:

sum(if(Status_OnTime_Late ='On Time',conta,0))/SUM({$<Status_OnTime_Late= >} conta)

But i need to add more in the dimension Status_OnTime_Late

for example this way but Doesn't work the "IN"  Operator:

=sum(if(Status_OnTime_Late in ('On Time','Late'),conta,0))/SUM({$<Status_OnTime_Late= >} conta)

HOw it work the operator "IN" from SQL on Qlik VIEW?

Regards!!!!

[jjordaan]

Hi Limonbi29,

In QlikView the functions Matc, Mixmatch and Wildmatch are equal to the function In from T-SQL.

Below you will find the explanantion from QlikView help:

match( str, expr1 [ , expr2,...exprN ] )

The match function performs a case sensitive comparison.

Example:

match( M, 'Jan','Feb','Mar')

returns 2 if M = Feb

returns 0 if M = Apr or jan

mixmatch( str, expr1 [ , expr2,...exprN ] )

The mixmatch function performs a case insensitive comparison.

Example:

mixmatch( M, 'Jan','Feb','Mar')

returns 1 if M = jan

wildmatch( str, expr1 [ , expr2,...exprN ] )

The wildmatch function performs a case insensitive comparison and permits the use of wildcard characters ( * and ?) in the comparison strings.

Example:

wildmatch( M, 'ja*','fe?','mar')

returns 1 if M = January

returns 2 if M = fex

[MayilVahanan]

HI

Try like this

= Sum({<Status_OnTime_Late = {'On Time','Late'} >} conta) / SUM({<Status_OnTime_Late= >} conta)

Use set analysis for better performance.

Hope it helps

[#4]

doesnt work sintaxis error

[MayilVahanan]

Hi

Did u try this

= Sum({<Status_OnTime_Late = {'On Time','Late'} >} conta) / SUM({<Status_OnTime_Late= >} conta)

[Sokkorn · Accepted solution]

Hi limonbi29,

Set analysis give you a better performance.

1. Set analysis method to get your job done

=Sum({$<Status_OnTime_Late = {'On Time','Late'} >} conta) / Sum({1} conta)

//This will reture sum amount of [conta] where [Status_OnTime_Late] in ('On Time','Late') divide  by sum TOTAL amount of [conta]

=Sum({$<Status_OnTime_Late = {'On Time','Late'} >} conta) / Sum({1} DISTINCT conta)

/This will reture sum amount of [conta] where [Status_OnTime_Late] in ('On Time','Late') divide  by sum TOTAL DISTINCT amount of [conta]

2. Use IF statement (not good performance)

=Sum(If(Match(Status_OnTime_Late ,'On Time','Late'),conta,0))/Sum({1} conta)

Or you wish to use wildcard

Sum(If(WildMatch(Status_OnTime_Late ,'*On Time*','*Late*'),conta,0))/Sum({1} conta)

Hope this help and clear.

Regards,

Sokkorn

[#7]

thanks for your help work it!!!!!!

regards!!!!