topic Re: How can I write this sql in qlikview. in QlikView

How can I write this sql in qlikview.

Wed, 11 Dec 2013 20:49:10 GMT

WHEN ROI_NBR_SESSIONS_2013.ITEM_ID = 'LR_R6R7'

AND (ROI_NBR_SESSIONS_2013.ACCOUNT_NO LIKE '00074%'

OR ROI_NBR_SESSIONS_2013.ACCOUNT_NO LIKE '00075%'

OR ROI_NBR_SESSIONS_2013.ACCOUNT_NO LIKE '0008%')

THEN SUBSTR(ROI_NBR_SESSIONS_2013.ACCOUNT_NO,1,11)

Re: How can I write this sql in qlikview.

Josh_Good — Wed, 11 Dec 2013 22:21:31 GMT

I'm assuming you are wanted to write this in an expression in the UI. I also assumed your field names are in the table.field structure because you used the Qualify statement:

If( ROI_NBR_SESSIONS_2013.ITEM_ID = 'LR_R6R7'

AND (ROI_NBR_SESSIONS_2013.ACCOUNT_NO LIKE '00074*'

OR ROI_NBR_SESSIONS_2013.ACCOUNT_NO LIKE '00075*'

OR ROI_NBR_SESSIONS_2013.ACCOUNT_NO LIKE '0008*')

, Left(ROI_NBR_SESSIONS_2013.ACCOUNT_NO,11))

If your fields are loaded into QlikView just a fields names (this is the default) then drop the table names from above. I.e. replace ROI_NBR_SESSIONS_2013.ACCOUNT_NO with
ACCOUNT_NO

Re: How can I write this sql in qlikview.

maxgro — Wed, 11 Dec 2013 22:57:11 GMT

I think the match function can help you with the or part of the expression

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.

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

returns 1 if M = January

returns 2 if M = fex

If( ROI_NBR_SESSIONS_2013.ITEM_ID = 'LR_R6R7'

AND wildmatch(ROI_NBR_SESSIONS_2013.ACCOUNT_NO, '00074*', '00075*', '0008*')

, Left(ROI_NBR_SESSIONS_2013.ACCOUNT_NO,11))