Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Im trying to get an IF statement to show a value captured or N/a if > or < than a certain amount

Hi there,

I need to highlight whether data captured is over a certain threshold.

Ideally I want to create an IF statement that if the value is >xx it shows the value captured and if not, it shows 'N/A'.

This is what I've tried.... IF([Trade Value] >(XX),[Trade Value],'N/A') as [Trade >xx]

It seems to load ok when reloading the data but the results are unexpected as I only receive N/A's when the data set has values greater than the threshold.

any help will earn you a coffee and a doughnut

10 Replies
Highlighted
MVP
MVP

Re: Im trying to get an IF statement to show a value captured or N/a if > or < than a certain amount

Well, what is (XX)? Where does the threshold come from?

If it's a variable called XX, you probably need to write:

IF([Trade Value] > $(XX),[Trade Value],'N/A') as [Trade >xx]


If it's a field, is this field located in the same table?

Highlighted

Re: Im trying to get an IF statement to show a value captured or N/a if > or < than a certain amount

You have to write a code like as i understand your requirement

IF([Trade Value] >(XX),[Trade Value],'N/A') as [Trade >xx]


Eg:-

IF( [Trade Value] > 200, [Trade Value] , 'N/A' ) as [Trade >xx]



Regards

Highlighted
Not applicable

Re: Im trying to get an IF statement to show a value captured or N/a if > or < than a certain amount

Try using the about statements in the scripts than directly in charts, if used at chart levels

Highlighted

Re: Im trying to get an IF statement to show a value captured or N/a if > or < than a certain amount

If you pass this threshold value to any variable like XX then use script like

Eg:-

Let XX = YourThresoldvalue;

Load

[Trade Value],

IF([Trade Value] > $(XX),[Trade Value],'N/A') as [Trade >xx]

From Location;

Regards

Highlighted
Not applicable

Re: Im trying to get an IF statement to show a value captured or N/a if > or < than a certain amount

wow these are some swift responses - thank you

SWUEHL - the xx's are 75000 so the statement i've (poorly) written is as follows;

IF([Trade Value] > 75000,[Trade Value],'N/A' as [Trade Value >75000]

and yes it is a field (within a SharePoint library)

Anand - thanks for the response, I have tried removing the brackets around the 75000 but it still just brings back a - rather than the figure in the table
I will give the the second one a go just now

Kiruthiga - thank you I will try this also (just need to learn about the about statements now

thanks to you all

Highlighted

Re: Im trying to get an IF statement to show a value captured or N/a if > or < than a certain amount

I explain you how it works Suppose a field [Trade Value] contains value

Eg:-

[Trade Value]

100000

75000

12000

120000

450

18524

158623

Load

[Trade Value],

IF([Trade Value] > 75000,[Trade Value],'N/A' as [Trade Value >75000]

From Location;


After Load of your statement you get data in Field [Trade Value >75000] like


Trade ValueTrade Value >75000
450N/A
12000N/A
18524N/A
75000N/A
100000100000
120000120000
158623158623


Like this you are doing if so please check

Regards

Highlighted
MVP
MVP

Re: Im trying to get an IF statement to show a value captured or N/a if > or < than a certain amount

I have not fully understood if 75000 is a literal or a field. Could you elaborate this a little, by posting your complete load statement?

Highlighted
Not applicable

Re: Im trying to get an IF statement to show a value captured or N/a if > or < than a certain amount

here it is;

Load

[FundsInformation/Trade Value] as [Notional Trade Value],
IF([Notional Trade Value] >75000000,[Notional Trade Value],'N/a') as [Notional Trade >£75m]

FROM [\\www.avivaworld.com@...........] (XmlSimple, Table is [myFields]);

many thanks for taking the time and effort - it really is appreciated

Highlighted
MVP
MVP

Re: Im trying to get an IF statement to show a value captured or N/a if > or < than a certain amount

Ah, you can't use the aliased field name in the same LOAD e.g. in a condition.

I think that you even should got a script error when trying so.

Instead, try

Load

[FundsInformation/Trade Value] as [Notional Trade Value],
IF([FundsInformation/Trade Value] >75000000,[FundsInformation/Trade Value],'N/a') as [Notional Trade >£75m]

FROM [\\www.avivaworld.com@...........] (XmlSimple, Table is [myFields]);

or use a preceding LOAD:

LOAD

     *,

     IF([Notional Trade Value] >75000000,[Notional Trade Value],'N/a') as [Notional Trade >£75m];

LOAD

[FundsInformation/Trade Value] as [Notional Trade Value]

...

FROM [\\www.avivaworld.com@...........] (XmlSimple, Table is [myFields]);

And make sure that your [FundsInformation/Trade Value] field is interpreted as a number, not a text (e.g. when containing currency symbols).