topic Re: IF statement not working as expected in App Development

IF statement not working as expected

RickF — Thu, 16 Nov 2023 14:54:58 GMT

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.

Re: IF statement not working as expected

Or — Thu, 16 Nov 2023 14:58:47 GMT

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.

Re: IF statement not working as expected

RickF — Thu, 16 Nov 2023 15:09:48 GMT

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

Re: IF statement not working as expected

marcus_sommer — Thu, 16 Nov 2023 16:10:25 GMT

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.

Re: IF statement not working as expected

Aasir — Thu, 16 Nov 2023 16:17:01 GMT

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

Re: IF statement not working as expected

RickF — Thu, 16 Nov 2023 16:36:49 GMT

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.