topic Re: If Condition? in QlikView

If Condition?

Anonymous — Fri, 07 Apr 2017 16:46:23 GMT

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?

Re: If Condition?

sunny_talwar — Fri, 07 Apr 2017 16:50:00 GMT

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',)))))

Re: If Condition?

Anil_Babu_Samineni — Fri, 07 Apr 2017 16:52:45 GMT

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')))))

Re: If Condition?

vinieme12 — Fri, 07 Apr 2017 16:54:12 GMT

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

Group filter?

Re: If Condition?

sunny_talwar — Fri, 07 Apr 2017 16:57:58 GMT

Nope, probably doesn't like you

Re: If Condition?

vinieme12 — Fri, 07 Apr 2017 16:58:10 GMT

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

Re: If Condition?

vinieme12 — Fri, 07 Apr 2017 16:58:24 GMT

Hahaha

Re: If Condition?

sunny_talwar — Fri, 07 Apr 2017 16:58:39 GMT

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

Re: If Condition?

Anonymous — Fri, 07 Apr 2017 17:08:39 GMT

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

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.

Re: If Condition?

Anonymous — Fri, 07 Apr 2017 17:11:17 GMT

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

Re: If Condition?

sunny_talwar — Fri, 07 Apr 2017 17:11:52 GMT

Then why didn't you use what vinieme12‌ gave you? You won't be able to replicate what you want using if statement. You might be able to use multiple loads with Where statement or use Interval Match, but if is probably not going to work

Re: If Condition?

Anonymous — Fri, 07 Apr 2017 17:15:56 GMT

I didn't understand what he did there. I am trying to make this work with a case or if functions. If it's not possible, then I will like to have an 'how to do the Vineeth Pujari solution for dumbs?'

Thank you.

Re: If Condition?

Kushal_Chawda — Fri, 07 Apr 2017 17:17:09 GMT

another way could be. Take resident load of your original table

Data:

LOAD Date,

Dim1,

Dim2,

autonumber(Date&Dim1&Dim2) as Key // Create key which includes all the dimensions of table

Day

FROM Source;

Group:

LOAD Key

'1 to 30 Days' as Bucket

Resident Data

where Day<=30;

concatenate(Group)

LOAD Key

'30 + Days' as Bucket

Resident Data

where Day>30;

concatenate(Group)

LOAD Key

'45 + Days' as Bucket

Resident Data

where Day>45;

concatenate(Group)

LOAD Key

'60 + Days' as Bucket

Resident Data

where Day>60;

concatenate(Group)

LOAD Key

'90 + Days' as Bucket

Resident Data

where Day>90;

Re: If Condition?

sunny_talwar — Fri, 07 Apr 2017 17:19:37 GMT

Why not just RowNo() as Key?

Re: If Condition?

Anonymous — Fri, 07 Apr 2017 17:26:53 GMT

Can I use this cancatenate in the if/case clause?

Re: If Condition?

vinieme12 — Fri, 07 Apr 2017 17:47:21 GMT

Check the script of the app I attached on the old thread that solution uses intervalmatch ()

Or you can use kushals procedure to Concatenate multiple table for each filter

Re: If Condition?

Kushal_Chawda — Sat, 08 Apr 2017 06:12:40 GMT

Can you please elaborate more? I don't think you can use it in if, also it will not be the better way to do it.

Re: If Condition?

Kushal_Chawda — Sat, 08 Apr 2017 06:16:56 GMT

I am not sure but should work. Defining key will be a good approach that what I feel.

Re: If Condition?

Anonymous — Sat, 08 Apr 2017 06:34:15 GMT

you could use intervalmatch()

Re: If Condition?

Kushal_Chawda — Sat, 08 Apr 2017 09:04:07 GMT

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

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.