Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
samantha92
New Contributor III

Changing a field value from -1 to 'Y'

Hi,

I am working with cost information for clients and each individual client needs to have a spend banding.

I have created the following expression to show the spend banding of anything less than £250.00 - It is currently showing as 0 and -1 figures and  I am unsure of how to change the figure to 'Y' rather than -1.

sum(TOTAL <[SWIFT ID]>if(len(SCHEDULESALTWEEKLYCOST)>0, SCHEDULESALTWEEKLYCOST,
if(len(SCHEDULESALTWEEKLYCOST)=0,STDWEEKCOST))) <='250.00'

Can anyone help? I have tried changing the values and I am having no luck. I am wondering if this is something to do with how I have created my expression and maybe that is wrong?

Much appreciated.

Sam

1 Solution

Accepted Solutions
daveamz01
Contributor III

Re: Changing a field value from -1 to 'Y'

Hi Samantha,

Try to enclose everything into another IF statement:

If(sum(TOTAL <[SWIFT ID]>if(len(SCHEDULESALTWEEKLYCOST)>0, SCHEDULESALTWEEKLYCOST,
if(len(SCHEDULESALTWEEKLYCOST)=0,STDWEEKCOST))) <='250.00' , 'Y', 'N')


Few notes:

- you don't need quotes when comparing to numbers <= '250.00'. In this case, Qlik is doing a string comparison instead of number comparison.

- I suppose the SCHEDULESALTWEEKLYCOST field is sometimes null since you use that if test. Have a look at Alt() function. It is faster and easier to read (e.g If(sum(TOTAL <[SWIFT ID]>  Alt(SCHEDULESALTWEEKLYCOST, STDWEEKCOST, 0) <= 250 , 'Y', 'N'))). If it doesn't work, consider to move the if test in the script to increase the front end performance (IF statements are pretty slow in expression)

Regards,

David

View solution in original post

2 Replies
daveamz01
Contributor III

Re: Changing a field value from -1 to 'Y'

Hi Samantha,

Try to enclose everything into another IF statement:

If(sum(TOTAL <[SWIFT ID]>if(len(SCHEDULESALTWEEKLYCOST)>0, SCHEDULESALTWEEKLYCOST,
if(len(SCHEDULESALTWEEKLYCOST)=0,STDWEEKCOST))) <='250.00' , 'Y', 'N')


Few notes:

- you don't need quotes when comparing to numbers <= '250.00'. In this case, Qlik is doing a string comparison instead of number comparison.

- I suppose the SCHEDULESALTWEEKLYCOST field is sometimes null since you use that if test. Have a look at Alt() function. It is faster and easier to read (e.g If(sum(TOTAL <[SWIFT ID]>  Alt(SCHEDULESALTWEEKLYCOST, STDWEEKCOST, 0) <= 250 , 'Y', 'N'))). If it doesn't work, consider to move the if test in the script to increase the front end performance (IF statements are pretty slow in expression)

Regards,

David

View solution in original post

samantha92
New Contributor III

Re: Changing a field value from -1 to 'Y'

Hi David,

That works a charm - thanks so much!

Really good advice with the Alt() function - I need to read up on enhancing my expression for performance.

Thank you again

Sam