topic Re: query in QlikView

query

Mon, 04 May 2015 00:47:46 GMT

is there is any other way to write this expression

=IF(WildMatch(Batch_Char_New,'A*','M*') ,vROAD_PMT,

IF(WildMatch(Batch_Char_New,'R*') ,vRAIL_PMT))

where vROAD_PMT

=([vSales_Qty(InLacs)] * sum([Road PMT_ROAD_PMT]))

+(([vSales_Qty(InLacs)]) * sum([ZCFA_INV_Road PMT_ROAD_PMT]))

vRAIL_PMT

=(([vSales_Qty(InLacs)]) * sum([Rail PMT_RAIL_PMT]))+

(([vSales_Qty(InLacs)]) * sum([ZCFA_INV_Rail PMT_RAIL_PMT]))

Re: query

jagan — Mon, 04 May 2015 03:20:48 GMT

HI,

Try like this

=IF(WildMatch(Batch_Char_New,'A*','M*') ,vROAD_PMT,

IF(WildMatch(Batch_Char_New,'R*') ,vRAIL_PMT))

where vROAD_PMT

=(sum([Road PMT_ROAD_PMT]) + sum([ZCFA_INV_Road PMT_ROAD_PMT])) * [vSales_Qty(InLacs)]

vRAIL_PMT

=(sum([Rail PMT_RAIL_PMT]) + sum([ZCFA_INV_Rail PMT_RAIL_PMT])) * [vSales_Qty(InLacs)]

Regards,

Jagan.

Re: query

ToniKautto — Mon, 04 May 2015 04:21:27 GMT

It is always to hard to recommend the best possible way of setting up logic with a sample with data. The data model structure will usually decide the most efficient way of doing things.

What is the problem with your current expression? You get incorrect results or is it slow? Why do you want to write it differently?

Re: query

jagan — Mon, 04 May 2015 05:28:12 GMT

Or you can try this using Set Analysis without If()

=RangeSum(vROAD_PMT, vRAIL_PMT)

where vROAD_PMT

=(sum({<Batch_Char_New={'A*','M*'}>}[Road PMT_ROAD_PMT]) + sum({<Batch_Char_New={'A*','M*'}>} [ZCFA_INV_Road PMT_ROAD_PMT])) * [vSales_Qty(InLacs)]

vRAIL_PMT

=(sum({<Batch_Char_New={'R*'}>} [Rail PMT_RAIL_PMT]) + sum({<Batch_Char_New={'R*'}>} [ZCFA_INV_Rail PMT_RAIL_PMT])) * [vSales_Qty(InLacs)]