Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!!
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
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
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
doesnt work sintaxis error
Hi
Did u try this
= Sum({<Status_OnTime_Late = {'On Time','Late'} >} conta) / SUM({<Status_OnTime_Late= >} conta)
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
thanks for your help work it!!!!!!
regards!!!!