topic Re: Expression from Case in QlikView

Expression from Case

priyarane — Tue, 12 Jan 2016 12:19:32 GMT

Hi Community,

I have a case statement and need to convert in Expression for ex- Sum(Sales). What is the best way to write below one

case when

NOT ((a.trading_book_type='T' and a.bis_product_category in ('CLAIM')) or a.bis_product_category in ('REPO', 'DERIVATIVE_OTC', 'CREDIT_DERIVATIVE')) and

a.method_original in ('FOU', 'ADV')

and a.asset_class_original not in ('CLAIM_SECURITIZATION','CLAIM_RESECURITIZATION','CLAIM_RERESECURITIZATION')

and ((a.asset_class_category_original != 'CLAIM_EQUITY' and a.asset_class_original not like '%EQT%') and a.asset_class_original not like 'CLAIM_CIU')

and a.securitized in ('F','U')

and a.stress_testing_scenario_id=0

and not (a.tranche_collateral_type='COLLATERALIZED' and a.collateral_bis_product in ('REP_NET_COL', 'OTC_NET_COL'))

and (a.risk_type in ('CRE', 'DIL', 'RVR') or (a.risk_type = 'STR' and a.delivery_type = 'FDE'))

and nvl(a.risk_type, 'CRE') != 'MAR'

and nvl(a.bis_product_category, 'MA_PRODUCTS') not in ('DEFAULT_FUND_CONTRIBUTION')

and nvl(a.GENERIC_REPORTING_DATE,'CURRENT_PERIOD_END') ='CURRENT_PERIOD_END' and a.bis_product_category is not null

Re: Expression from Case

Peter_Cammaert — Tue, 12 Jan 2016 12:38:24 GMT

Expression in Load script or expression in user interface?

Re: Expression from Case

priyarane — Tue, 12 Jan 2016 12:40:10 GMT

Right now, I have to update in Expression... may be in future I will update in Script

Re: Expression from Case

Peter_Cammaert — Tue, 12 Jan 2016 13:31:01 GMT

You realise that it is never a good idea to translate SQL code as-is into QlikView expressions (if possible at all)?

Can you describe the business rule that the expression should implement?

Peter

Re: Expression from Case

priyarane — Tue, 12 Jan 2016 13:33:06 GMT

Is it possible at script level?

Re: Expression from Case

jonathandienst — Tue, 12 Jan 2016 13:44:30 GMT

You can implement this in script, with the following issues:

your expression (as posted) is incomplete (when clause, no then/else/end)
you will need to bring all the fields in this statement into QV
if this statement is filtering the load from the database, you may need to fetch a larger dataset to filter in QV