Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
mp802377
Creator II
Creator II
‎2017-02-22 10:24 AM

Error in Expression ')' complex IF Statement

Hello,

Without this IF statement it runs fine. I cannot see what I am missing.At first I had

IF(  (cccccccccccc.last_name   <> '' and cccccccccccc.first_name + ' ' + cccccccccccc.last_name    <> 'John Smith'), (ccccccccccc.last_name + ' ' + ccccccccccc.first_name),

IF(  (ccccccccccc.last_name     <> '' and ccccccccccc.first_name + ' ' + ccccccccccc.last_name       <> 'John Smith'), (cccccccccc.last_name + ' ' + cccccccccc.first_name),

IF(  (cccccccccc.last_name       <> '' and cccccccccc.first_name + ' ' + cccccccccc.last_name          <> 'John Smith'), (ccccccccc.last_name + ' ' + ccccccccc.first_name),

IF(  (ccccccccc.last_name        <> '' and ccccccccc.first_name + ' ' + ccccccccc.last_name              <> 'John Smith'), (cccccccc.last_name + ' ' + cccccccc.first_name),

IF(  (cccccccc.last_name         <> '' and cccccccc.first_name + ' ' + cccccccc.last_name                  <> 'John Smith'), (ccccccc.last_name + ' ' + ccccccc.first_name,

IF(  (ccccccc.last_name          <> '' and ccccccc.first_name + ' ' + ccccccc.last_name                      <> 'John Smith'), (cccccc.last_name + ' ' + cccccc.first_name),

IF(  (cccccc.last_name           <> '' and cccccc.first_name + ' ' + cccccc.last_name                          <> 'John Smith'), (ccccc.last_name + ' ' + ccccc.first_name),

IF(  (ccccc.last_name            <> '' and ccccc.first_name + ' ' + ccccc.last_name                              <> 'John Smith'), (cccc.last_name + ' ' + cccc.first_name),

IF(  (cccc.last_name             <> '' and cccc.first_name + ' ' + cccc.last_name                                  <> 'John Smith'), (ccc.last_name + ' ' + ccc.first_name),

IF(  (ccc.last_name              <> '' and ccc.first_name + ' ' + ccc.last_name                                      <> 'John Smith'), (cc.last_name + ' ' + cc.first_name),

IF(  (cc.last_name                <> '' and cc.first_name + ' ' + cc.last_name                                         <> 'John Smith'), (cc.last_name + ' ' + cc.first_name),'UNKNOWN')))))))))))) AS [leader]

And after reading some discussions, I changed it to below hoping that would fix it. Unfortunately I still get the error. This was created in Visual Studio with a CASE WHEN ELSE statement and I changed it to and IF.

IF(  (ISNULL(cccccccccccc.last_name)  and cccccccccccc.first_name + ' ' + cccccccccccc.last_name    <> 'John Smith'), (ccccccccccc.last_name + ' ' + ccccccccccc.first_name),

IF(  (ISNULL(ccccccccccc.last_name)   and ccccccccccc.first_name + ' ' + ccccccccccc.last_name       <> 'John Smith'), (cccccccccc.last_name + ' ' + cccccccccc.first_name),

IF(  (ISNULL(cccccccccc.last_name)    and cccccccccc.first_name + ' ' + cccccccccc.last_name          <> 'John Smith'), (ccccccccc.last_name + ' ' + ccccccccc.first_name),

IF(  (ISNULL(ccccccccc.last_name)      and ccccccccc.first_name + ' ' + ccccccccc.last_name              <> 'John Smith'), (cccccccc.last_name + ' ' + cccccccc.first_name),

IF(  (ISNULL(cccccccc.last_name)       and cccccccc.first_name + ' ' + cccccccc.last_name                  <> 'John Smith'), (ccccccc.last_name + ' ' + ccccccc.first_name,

IF(  (ISNULL(ccccccc.last_name)         and ccccccc.first_name + ' ' + ccccccc.last_name                      <> 'John Smith'), (cccccc.last_name + ' ' + cccccc.first_name),

IF(  (ISNULL(cccccc.last_name)          and cccccc.first_name + ' ' + cccccc.last_name                          <> 'John Smith'), (ccccc.last_name + ' ' + ccccc.first_name),

IF(  (ISNULL(ccccc.last_name)            and ccccc.first_name + ' ' + ccccc.last_name                              <> 'John Smith'), (cccc.last_name + ' ' + cccc.first_name),

IF(  (ISNULL(cccc.last_name)              and cccc.first_name + ' ' + cccc.last_name                                  <> 'John Smith'), (ccc.last_name + ' ' + ccc.first_name),

IF(  (ISNULL(ccc.last_name)                and ccc.first_name + ' ' + ccc.last_name                                      <> 'John Smith'), (cc.last_name + ' ' + cc.first_name),

IF(  (ISNULL(cc.last_name)                  and cc.first_name + ' ' + cc.last_name                                         <> 'John Smith'), (cc.last_name + ' ' + cc.first_name),'UNKNOWN')))))))))))) AS [leader]

Thank you,

Martha Parsons

1,698 Views
0 Likes
1 Solution

Accepted Solutions
vishsaggi
Champion III
Champion III
‎2017-02-22 10:33 AM

May be try this?

= IF(  Len(Trim(cccccccccccc.last_name))= 0  and cccccccccccc.first_name + ' ' + cccccccccccc.last_name    <> 'John Smith', ccccccccccc.last_name + ' ' + ccccccccccc.first_name,

IF(  Len(Trim(ccccccccccc.last_name))= 0      and ccccccccccc.first_name + ' ' + ccccccccccc.last_name       <> 'John Smith', cccccccccc.last_name + ' ' + cccccccccc.first_name,

IF(  Len(Trim(cccccccccc.last_name))= 0       and cccccccccc.first_name + ' ' + cccccccccc.last_name          <> 'John Smith',  ccccccccc.last_name + ' ' + ccccccccc.first_name,

IF(  Len(Trim(ccccccccc.last_name))= 0        and ccccccccc.first_name + ' ' + ccccccccc.last_name              <> 'John Smith', cccccccc.last_name + ' ' + cccccccc.first_name,

IF(  Len(Trim(cccccccc.last_name))= 0         and cccccccc.first_name + ' ' + cccccccc.last_name                  <> 'John Smith', ccccccc.last_name + ' ' + ccccccc.first_name

IF(  Len(Trim(ccccccc.last_name))= 0          and ccccccc.first_name + ' ' + ccccccc.last_name                      <> 'John Smith', cccccc.last_name + ' ' + cccccc.first_name,

IF(  Len(Trim(cccccc.last_name))= 0           and cccccc.first_name + ' ' + cccccc.last_name                          <> 'John Smith', ccccc.last_name + ' ' + ccccc.first_name,

IF(  Len(Trim(ccccc.last_name))= 0            and ccccc.first_name + ' ' + ccccc.last_name                              <> 'John Smith', cccc.last_name + ' ' + cccc.first_name,

IF(  Len(Trim(cccc.last_name))= 0             and cccc.first_name + ' ' + cccc.last_name                                  <> 'John Smith', ccc.last_name + ' ' + ccc.first_name,

IF(  Len(Trim(ccc.last_name))= 0              and ccc.first_name + ' ' + ccc.last_name                                      <> 'John Smith', cc.last_name + ' ' + cc.first_name,

IF(  Len(Trim(cc.last_name))= 0               and cc.first_name + ' ' + cc.last_name                                         <> 'John Smith', cc.last_name + ' ' + cc.first_name,'UNKNOWN'

  ))))))))))) AS [leader]

View solution in original post

1,573 Views
4 Replies
sunny_talwar
MVP
MVP
‎2017-02-22 10:29 AM

Try replace + with &

IF(  (cccccccccccc.last_name   <> '' and cccccccccccc.first_name & ' ' & cccccccccccc.last_name    <> 'John Smith'), (ccccccccccc.last_name + ' ' + ccccccccccc.first_name),

1,573 Views
vishsaggi
Champion III
Champion III
‎2017-02-22 10:33 AM

May be try this?

= IF(  Len(Trim(cccccccccccc.last_name))= 0  and cccccccccccc.first_name + ' ' + cccccccccccc.last_name    <> 'John Smith', ccccccccccc.last_name + ' ' + ccccccccccc.first_name,

IF(  Len(Trim(ccccccccccc.last_name))= 0      and ccccccccccc.first_name + ' ' + ccccccccccc.last_name       <> 'John Smith', cccccccccc.last_name + ' ' + cccccccccc.first_name,

IF(  Len(Trim(cccccccccc.last_name))= 0       and cccccccccc.first_name + ' ' + cccccccccc.last_name          <> 'John Smith',  ccccccccc.last_name + ' ' + ccccccccc.first_name,

IF(  Len(Trim(ccccccccc.last_name))= 0        and ccccccccc.first_name + ' ' + ccccccccc.last_name              <> 'John Smith', cccccccc.last_name + ' ' + cccccccc.first_name,

IF(  Len(Trim(cccccccc.last_name))= 0         and cccccccc.first_name + ' ' + cccccccc.last_name                  <> 'John Smith', ccccccc.last_name + ' ' + ccccccc.first_name

IF(  Len(Trim(ccccccc.last_name))= 0          and ccccccc.first_name + ' ' + ccccccc.last_name                      <> 'John Smith', cccccc.last_name + ' ' + cccccc.first_name,

IF(  Len(Trim(cccccc.last_name))= 0           and cccccc.first_name + ' ' + cccccc.last_name                          <> 'John Smith', ccccc.last_name + ' ' + ccccc.first_name,

IF(  Len(Trim(ccccc.last_name))= 0            and ccccc.first_name + ' ' + ccccc.last_name                              <> 'John Smith', cccc.last_name + ' ' + cccc.first_name,

IF(  Len(Trim(cccc.last_name))= 0             and cccc.first_name + ' ' + cccc.last_name                                  <> 'John Smith', ccc.last_name + ' ' + ccc.first_name,

IF(  Len(Trim(ccc.last_name))= 0              and ccc.first_name + ' ' + ccc.last_name                                      <> 'John Smith', cc.last_name + ' ' + cc.first_name,

IF(  Len(Trim(cc.last_name))= 0               and cc.first_name + ' ' + cc.last_name                                         <> 'John Smith', cc.last_name + ' ' + cc.first_name,'UNKNOWN'

  ))))))))))) AS [leader]

1,574 Views
Anil_Babu_Samineni
MVP
MVP
‎2017-02-22 10:34 AM

In response to sunny_talwar

May be typo error

IF(  (cccccccccccc.last_name   <> '' and cccccccccccc.first_name & ' ' & cccccccccccc.last_name    <> 'John Smith'), (ccccccccccc.last_name & ' ' & ccccccccccc.first_name),

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
1,573 Views
MarcoWedel
MVP
MVP
‎2017-02-22 06:41 PM

You missed one bracket in your original expression (and put it at the end instead):

IF(  (cccccccccccc.last_name   <> '' and cccccccccccc.first_name + ' ' + cccccccccccc.last_name    <> 'John Smith'), (ccccccccccc.last_name + ' ' + ccccccccccc.first_name),

IF(  (ccccccccccc.last_name     <> '' and ccccccccccc.first_name + ' ' + ccccccccccc.last_name       <> 'John Smith'), (cccccccccc.last_name + ' ' + cccccccccc.first_name),

IF(  (cccccccccc.last_name       <> '' and cccccccccc.first_name + ' ' + cccccccccc.last_name          <> 'John Smith'), (ccccccccc.last_name + ' ' + ccccccccc.first_name),

IF(  (ccccccccc.last_name        <> '' and ccccccccc.first_name + ' ' + ccccccccc.last_name              <> 'John Smith'), (cccccccc.last_name + ' ' + cccccccc.first_name),

IF(  (cccccccc.last_name         <> '' and cccccccc.first_name + ' ' + cccccccc.last_name                  <> 'John Smith'), (ccccccc.last_name + ' ' + ccccccc.first_name),

IF(  (ccccccc.last_name          <> '' and ccccccc.first_name + ' ' + ccccccc.last_name                      <> 'John Smith'), (cccccc.last_name + ' ' + cccccc.first_name),

IF(  (cccccc.last_name           <> '' and cccccc.first_name + ' ' + cccccc.last_name                          <> 'John Smith'), (ccccc.last_name + ' ' + ccccc.first_name),

IF(  (ccccc.last_name            <> '' and ccccc.first_name + ' ' + ccccc.last_name                              <> 'John Smith'), (cccc.last_name + ' ' + cccc.first_name),

IF(  (cccc.last_name             <> '' and cccc.first_name + ' ' + cccc.last_name                                  <> 'John Smith'), (ccc.last_name + ' ' + ccc.first_name),

IF(  (ccc.last_name              <> '' and ccc.first_name + ' ' + ccc.last_name                                      <> 'John Smith'), (cc.last_name + ' ' + cc.first_name),

IF(  (cc.last_name                <> '' and cc.first_name + ' ' + cc.last_name                                         <> 'John Smith'), (cc.last_name + ' ' + cc.first_name),'UNKNOWN')))))))))))) AS [leader]

hope this helps

regards

Marco

1,573 Views
0 Likes