Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
RickF
Partner - Contributor II
Partner - Contributor II

IF statement not working as expected

Hi all,

I have an issue trying to change the value of a field in a table using an if statement that is not working.

I made a Link Table with different field and for a new project I need to change the value of a field of this link table.
Let's say my link table is called LT_temp and has 7 fields. I need to change the value of a single field that can be 'A' or 'B' as follows:
                      if field value equals 'A' then field value must become 'AB', else it must remain 'B'.

I tryed something like this:
LT:
Load
          field1,
          field2,
          field3,
          field4,
          field5,
          if(field6='A', 'AB', field6) as field6,
          field7
Resident LT_temp;
Drop Table LT_temp;

But as soon as i run the code, as a result I only have 'B' as value for field6.

I can't figure out how to solve it...help is very much appreciated.

Labels (1)
1 Solution

Accepted Solutions
RickF
Partner - Contributor II
Partner - Contributor II
Author

With saying "field6 exists within any other table" you helped me discovering that field6 is a field in another table used for section access.

In the end the problem wasn't actually related to the if statement, it was related to the section access.
The new value 'AB' was not a possible value for the section access file used and because of this I wasn't able anymore to see data with field6='AB'.

Adding 'AB' as a possible value for field6 in the file used for section access It turned out that all is now working as expected.

View solution in original post

5 Replies
Or
MVP
MVP

I'd suggest confirming the value is actually 'A'. There may be hidden spaces (in which case you'd want to trim() the value) or other non-visible characters.

You'll also want to use Noconcatenate Load here, since you're using the exact same field names as the original table. Your issue might be that you are unintentionally dropping both iterations.

RickF
Partner - Contributor II
Partner - Contributor II
Author

You're right, i need to use NoConcatenate load as I did but forgot to write in the example...


As suggested I tried to use trim(field6) but it results in the same behavior as without trim.

I think that there should not be any non-visible character because creating a completely new field based on the same if statement works as expected.

NoConcatenate
LT:
Load
          field1,
          field2,
          field3,
          field4,
          field5,
          field6,
          if(field6='A', 'AB', field6) as field6_new,

          field7
Resident LT_temp;
Drop Table LT_temp;

Problem is I can't use a new field, I must use the old one with the new code AB instead of A

marcus_sommer

If it's working with this new field you may add:

NoConcatenate
LT:
Load
          field1,
          field2,
          field3,
          field4,
          field5,
          field6,
          if(field6='A', 'AB', field6) as field6_new,

          field7
Resident LT_temp;
Drop Table LT_temp;

drop fields field6 from LT; rename field6_new to field6;

If it failed because field6 exists within any other table you may change the adjustment-query to:

if(keepchar(field6, 'AB') ='A', 'AB', field6) as field6

Beside this it not necessary to apply such change within a resident-load on a temp-table else it could be already applied within the origin-load.

 

Aasir
Creator III
Creator III

Try this
if(Upper(Trim(field6)) = 'A', 'AB', field6) as field6,

 

RickF
Partner - Contributor II
Partner - Contributor II
Author

With saying "field6 exists within any other table" you helped me discovering that field6 is a field in another table used for section access.

In the end the problem wasn't actually related to the if statement, it was related to the section access.
The new value 'AB' was not a possible value for the section access file used and because of this I wasn't able anymore to see data with field6='AB'.

Adding 'AB' as a possible value for field6 in the file used for section access It turned out that all is now working as expected.