If Condition?

[Anonymous]

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?

[sunny_talwar]

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

[Anonymous]

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.

[Kushal_Chawda]

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;

[sunny_talwar]

Why not just RowNo() as Key?

[Anonymous]

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

[vinieme12]

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

[Kushal_Chawda]

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.

[Kushal_Chawda]

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

[Anonymous]

you could use intervalmatch()

[Kushal_Chawda · Accepted solution]

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.