How to concatenate string with measure values under case statement

[inu]

Hi Team

I have one requirement , as below

case
when [Candidates Hired]>5000 then (([Candidates Hired]*10) ||'M')
when [Candidates Hired]>6000 then (([Candidates Hired]*100) ||'MM')
else [Candidates Hired]
end

Above syntax is not correct becasue [Candidates Hired] is measure item,
Please suggest how to resolve the issue.


Regards,
Inam

[MFGF]

Hi Team

I have one requirement , as below

case
when [Candidates Hired]>5000 then (([Candidates Hired]*10) ||'M')
when [Candidates Hired]>6000 then (([Candidates Hired]*100) ||'MM')
else [Candidates Hired]
end

Above syntax is not correct becasue [Candidates Hired] is measure item,
Please suggest how to resolve the issue.


Regards,
Inam

Where are you displaying this? In a list? Crosstab? Something else?

If a list (and assuming you don't need to aggregate the measure) you could try:

case
when [Candidates Hired]>5000 then ((cast([Candidates Hired]*10, varchar(12))) +'M')
when [Candidates Hired]>6000 then ((cast([Candidates Hired]*100, varchar(12))) +'MM')
else cast([Candidates Hired], varchar(12))
end

This assumes you are using a relational package...

MF.

[inu]

Where are you displaying this? In a list? Crosstab? Something else?

If a list (and assuming you don't need to aggregate the measure) you could try:

case
when [Candidates Hired]>5000 then ((cast([Candidates Hired]*10, varchar(12))) +'M')
when [Candidates Hired]>6000 then ((cast([Candidates Hired]*100, varchar(12))) +'MM')
else cast([Candidates Hired], varchar(12))
end

This assumes you are using a relational package...

MF.

Thanks MF..

chart is being used, and ,dimensional package is being used...

I will try and let you know ...



Regards,
inam

[MFGF]

Thanks MF..

chart is being used, and ,dimensional package is being used...

I will try and let you know ...



Regards,
inam

In that case it's not going to work. You can't plot measure values that are character strings in a chart, and I doubt you will be able to cast them anyway if you are using an OLAP source.

MF.