topic Re: sql to expression in QlikView

sql to expression

manoranjan_d — Thu, 20 Jul 2017 07:38:59 GMT

(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?

Re: sql to expression

prma7799 — Thu, 20 Jul 2017 07:48:00 GMT

Try like this

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

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

Or share some sample data with expected output.

Re: sql to expression

manoranjan_d — Thu, 20 Jul 2017 07:54:46 GMT

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 ' '

Re: sql to expression

Thu, 20 Jul 2017 07:57:05 GMT

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

Re: sql to expression

Kushal_Chawda — Thu, 20 Jul 2017 09:19:44 GMT

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

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

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

Re: sql to expression

effinty2112 — Thu, 20 Jul 2017 09:31:54 GMT

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