topic Re: Sorting expression in QlikView

Sorting expression

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have data that needs to be sorted into specific groups. The data is going to updated each night so can't add anything into the LOAD script.

The data is in the following format

File UniqueRef Amount

ABC 123abc 4.6

BEE 112dfd 12

DEE 443asd -0.52

What I have been trying to do is sort them into groups where Amount < 0 would be put into that group/column

amount > 12 into another group

Amount <5 etc.

So far in the dimension i have the following which WORKS. - But i can't replicate into the expression.

=if ([Amount] <5, '<5%',

if ([Amount] <=7 , '6-7%',

if ([Amount] >10, '>10%',

))))))

With Count(unique ref)as expression

How can i put everything in the expression - Leaving just file name as the dimension - I have tried in the expression

count(([UniqueRef]) and [amount] <5)

but it doesn't work.

Thanks

Anonymous — Mon, 04 Dec 2017 12:21:42 GMT

try this

count({<$[amount]={'(='<5')'}>}[UniqueRef])

Anil_Babu_Samineni — Mon, 04 Dec 2017 12:24:15 GMT

One solution may be

=if ([Amount] <5, Dual('<5%', 1), if ([Amount] <=7 , Dual('6-7%',2), if ([Amount] >10, Dual('>10%',3)))) as FieldName

And expression is

count({<FieldName = {'<5%'}>} UniqueRef)

Anonymous — Mon, 04 Dec 2017 12:35:03 GMT

That formula is pretty much the same one I tried in my expression too - Issue is the numbers are wrong that it produces.

Anil_Babu_Samineni — Mon, 04 Dec 2017 12:37:58 GMT

May be negative values also considering as <5% in your expression that you don't want?