Evaluation in variable

[Anonymous]

I have created one variable Var1 as

ULnNo is line # from Table1 (Island table and no connection with any table)

All rests fromTable2

If(ULnNo = 'UCX2',Sum({$<QUARTER = {1}, SCENARIO_ID = {1}, LINE_NUMBER = {'13','23'}>} PROJECTION_VALUE)/1000000,
If(ULnNo = 'UCX4',Sum({$<QUARTER = {1}, SCENARIO_ID = {1}, LINE_NUMBER = {'56','59'}>} PROJECTION_VALUE)/1000000,
If(ULnNo = 'UCX7',Sum({$<QUARTER = {1}, SCENARIO_ID = {1}, LINE_NUMBER = {'98','102'}>} PROJECTION_VALUE)/1000000,
                             Sum({$<QUARTER = {1}, SCENARIO_ID = {1}, LINE_NUMBER = {'$(=ULnNo)'}>} PROJECTION_VALUE)/1000000      ???
)))

The fourth ELSE condition(???) does not fulfil, what mistake I am making here please?

I tried number of ways but not work

Thanking you all

[maxgro]

if ULnNo is a field

replace  LINE_NUMBER = {'$(=ULnNo)'}>}

with

LINE_NUMBER={'$(=concat(distinct ULnNo, chr(39) & ',' & chr(39)))'}>}

[Anonymous]

Hi Massimo

Thanks for the help

BTW, I do not get LINE_NUMBER, I can see null or empty

[#4]

Hi,

Try this

If(ULnNo = 'UCX2',Sum({$<QUARTER = {1}, SCENARIO_ID = {1}, LINE_NUMBER = {'13','23'}>} PROJECTION_VALUE)/1000000,
If(ULnNo = 'UCX4',Sum({$<QUARTER = {1}, SCENARIO_ID = {1}, LINE_NUMBER = {'56','59'}>} PROJECTION_VALUE)/1000000,
If(ULnNo = 'UCX7',Sum({$<QUARTER = {1}, SCENARIO_ID = {1}, LINE_NUMBER = {'98','102'}>} PROJECTION_VALUE)/1000000,
                             Sum({$<QUARTER = {1}, SCENARIO_ID = {1}, LINE_NUMBER = {'$(=$(ULnNo))'}>}PROJECTION_VALUE)/1000000      ???
)))

[Anonymous]

Hi Shruthi

Thanks for your reply

Steel some problem,

By the way whole script I have created as Variable (say vCol1) and I am evaluating under expression as

=$(vCol1)

By this I only can see values where UCX2, UCX4, UCX7 are. No others

Thanks

Raj