Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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.
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;
Why not just RowNo() as Key?
Can I use this cancatenate in the if/case clause?
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
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.
I am not sure but should work. Defining key will be a good approach that what I feel.
you could use intervalmatch()
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.