topic Re: Set Analysis with zero length / null in QlikView

Set Analysis with zero length / null

mikelees80 — Mon, 06 Nov 2017 16:37:57 GMT

HI All

I'm trying to write some set analysis to give all the orders we have that is missing a item division due to incorrect set up

here is my currently set analysis

sum({$<[Item - IH Division]={"=len(trim([Item - IH Division]))=0"}>} [Quantity - Ordered])

I've read multiple threads on here saying this should work example Blank or NULL in Set Analysis however i can't get it to work.

I don't want to use an if statement becuase that hits performance

Any ideas?

Anil_Babu_Samineni — Mon, 06 Nov 2017 16:47:41 GMT

What is the issue with this expression? Seems okay to me

sunny_talwar — Mon, 06 Nov 2017 17:10:48 GMT

May be create a flag in the script

If(Len(Trim([Item - IH Division])) = 0, 1, 0) as Flag

and then this

Sum({$<Flag = {1}>} [Quantity - Ordered])

mikelees80 — Mon, 06 Nov 2017 20:21:51 GMT

My quantity ordered returns 0

example set analysis i have tried, using an If statment works but not set analysis

Item - IH Division	sum({$<[Item - IH Division]={"=len(trim([Item - IH Division]))>=0"}>} [Quantity - Ordered])	sum([Quantity - Ordered])	sum({$<[Item - IH Division]={"=len(trim([Item - IH Division]))=0"}>} [Quantity - Ordered])	sum(if(len(trim([Item - IH Division]))=0,[Quantity - Ordered],0))	if(len(trim([Item - IH Division]))>0,sum([Quantity - Ordered]),0)
BREAST HEALTH	1,166,294	1,166,294	0	0	1,166,294
DIAGNOSTIC	95,400,804	95,400,804	0	0	95,400,804
FREIGHT	8,398	8,398	0	0	8,398
OTHER	551,589	551,589	0	0	551,589
SKELETAL HEALTH	100,992	100,992	0	0	100,992
SURGICAL	427,281	427,281	0	0	427,281
TBD	344,846	344,846	0	0	344,846
-	0	25,748,176	0	101,471,615	0

rwunderlich — Tue, 07 Nov 2017 02:29:27 GMT

It would be useful to see a screenshot of the tablemodel.

-Rob