sql to expression

[manoranjan_d]

(CASE WHEN ((table1.lastname IS NOT NULL) OR (table1.firstname IS NOT NULL)) THEN ISNULL(table1.lastname ,' ') + ', ' +

ISNULL(table1.firstname ,' ') ELSE table2.namemodify by END) AS modifiedname,

how to do this in expression?

[prma7799]

Try like this

=if( not IsNull (lastname) or not IsNull  ( firstname ) ,

IsNull (lastname) & IsNull (firstname) , namemodify)

Or share some sample data with expected output.

[manoranjan_d]

PM "not isnull" function is not in qlikview and then pls see the condition in the sql query we have space

below is description

THEN ISNULL(table1.lastname ,' ') + ', ' +

ISNULL(table1.firstname ,' ')

after lastname and firstname see the comma and space in the single quotes ' '

[#4]

Dear Manoranjan,

Try this.

If(IsNull(table1.lastname)=0,Concat(table1.lastname&' '&table1.firstname),if(IsNull(table1.firstname)=0,Concat(table1.lastname&' '&table1.firstname),table2.namemodify))

as modifiedname,

where in place of table1.lastname,table1.firstname,table2.namemodify put the corresponding variable name.

This expression will check whether table1.last name is not null and if yes give the value table1.lastname along with table1.firstname.

Then it will check whether table1.firstname is not null and if yes will give the value table1.lastname along with table1.firstname.

If both of them is null will return the value table2.namemodify

[Kushal_Chawda]

if( len(trim(lastname))>0  or len(trim(firstname ))>0 ,

if(len(trim(lastname))=0,' ',lastname) & ','

if(len(trim(firstname))=0,' ',firstname) ,namemodify)

[effinty2112]

Hi,

Here's another suggestion;

=if(len(trim(firstname))+ len(trim(lastname)) = 0, namemodify,

lastname & if(len(trim(firstname)) *len(trim(lastname)) >0,', ') & firstname)

This will only return a sting with a comma if firstname and lastname are both non-null.

cheers

Andrew