Solved: Re: If Condition? - Qlik Community

Anonymous · ‎2017-04-07

Hi,

I am trying to created a bucket but it's not working as I thought.

The idea is set ranges, but not excluding the other options. For example, I have a field called Days I have set the following:

If (Day =< 30, '1 to 30 Days',

If (Day > 30, '30 + Days',

If (Day > 45, '45 + Days',

If (Day > 60, '60 + Days',

If (Day > 90, '90 + Days',)))))

The problem here is if I want to have all above 30, I need to select 30 + Days, 45 + Days, 60 + Days and 90 + Days.

What I want is if I select 30 + Days it should include the other selections and no the range between 30 and 45.

This is in a List Box with field as expression.

Can you please help me with this?

Kushal_Chawda · ‎2017-04-08

another approach could be using cross table

Data:

LOAD *,

RowNo() as Key,

if(Days<=30,dual('1 to 30 Days',1)) as [Bucket<=30],

if(Days>30,dual('30 +Days',2)) as [Bucket>30],

if(Days>45,dual('45 +Days',3)) as [Bucket>45],

if(Days>60,dual('60 +Days',4)) as [Bucket>60],

if(Days>90,dual('90 +Days',5)) as [Bucket>90]

INLINE [

Days

10

20

30

13

56

76

45

89

34

44

13

88

92 ];

Bucket:

CrossTable(Value,Bucket)

LOAD Key,

[Bucket<=30] ,

[Bucket>30],

[Bucket>45],

[Bucket>60],

[Bucket>90]

Resident Data;

DROP Fields Value;

// DROP Fields [Bucket<=30],[Bucket>30], [Bucket>45],[Bucket>60],[Bucket>90] ;

You can drop the Bucket fields if not needed.

View solution in original post

sunny_talwar · ‎2017-04-07

Try this

If (Day <= 30, '1 to 30 Days',

If (Day < 45, '30 + Days',

If (Day < 60, '45 + Days',

If (Day < 90, '60 + Days',

If (Day >= 90, '90 + Days',)))))

Anil_Babu_Samineni · ‎2017-04-07

OR this? Typo error sunny with comma

If (Day <= 30, '1 to 30 Days',

If (Day >30 and Day <= 45, '30 + Days',

If (Day >45 and Day <= 60, '45 + Days',

If (Day >60 and Day <= 90, '60 + Days',

If (Day > 90, '90 + Days')))))

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

vinieme12 · ‎2017-04-07

Did you try the solution I posted on your old thread ?

Group filter?

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

sunny_talwar · ‎2017-04-07

Nope, probably doesn't like you

vinieme12 · ‎2017-04-07

The problem doing this is that if we take number 50 for example should be included in both filters

30+ days and 45 + days

If we use if() then a number will only be assigned to one filter

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

vinieme12 · ‎2017-04-07

Hahaha

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

sunny_talwar · ‎2017-04-07

Does that give any error? I know its a typo, but I feel like QlikView still doesn't throw any error, but I might be wrong

Anonymous · ‎2017-04-07

Anil and Sunny, that didn't work. I am looking for accumulative, I mean if I have for example these records:
1

4

31

35

45

48

60

65

90

95

110

When I select 30 + Days it should bring everything except by 1 and 4

When I select 45 + Days, it should include 45, 48, 60, 65, 90, 95 and 110

When I select 60 + Days, it should include 60,65,90,95 and 110

and so...

Is it possible to do it? I have tried with or/and conditions like

if (day >30 or day >45 or day >60 or day >90, etc

but this didn't work.

Anonymous · ‎2017-04-07

I am sorry Vineeth, I didn't understand it.