Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
How to create a filter based on below conditions.
I have a column with values 1,2,3,4….,52
I need filter based on
if = 1 then delivery 1
if <= 4 then delivery 4 (this should also include 1)
if <= 6 then delivery 6 (this should include both the above conditions)
Thanks
You need to create a separate table to associate WeekNo with the Deliveries Grouping
So you will have FactTable with Global_Key and WeekNo and another Table with a one to Many relationship with the Deliveries grouping
as below
You can do it in Two Ways
1)
FactTable:
Load GlobalKey,WeekNo
From SomeSourceTable;
WeekGroup:
Load
WeekNoFrom+iterno()-1 as WeekNo
,Deliveries
While WeekNoFrom+iterno()-1 <=WeekNoTo
;
load * inline [
WeekNoFrom,WeekNoTo,Deliveries
1,1,Last1Week
1,4,Last4Week
1,6,Last6Week
1,13,Last13Week
1,26,Last26Week
1,52,Last52Week
];
Concatenate(WeekGroup)
Load Distinct WeekNo,'ALL'
Resident FactTable;
Option 2:
FactTable:
Load GlobalKey,WeekNo
From SomeSourceTable;
WeekGroup:
Load Distinct WeekNo,'Last1Week' as Deliveries
Resident FactTable
Where WeekNo=1;
Concatenate(WeekGroup)
Load Distinct WeekNo,'Last4Week' as Deliveries
Resident FactTable
Where WeekNo<=4;
Concatenate(WeekGroup)
Load Distinct WeekNo,'Last6Week' as Deliveries
Resident FactTable
Where WeekNo<=6;
.
.and
.so
.on
.for each grouping
.
.
Concatenate(WeekGroup)
Load Distinct WeekNo,'ALL'
Resident FactTable;
As per the syntax if(condition , then [, else]) like this
If(Column = 1 ,'Delivery 1',
If(Column <= 4 ,'Delivery 4',
If(Column <= 6 ,'Delivery 6'))) as Filter
Hello Brun,
I am also using the same logic, but it is showing the results as below.
where for Delivery 1 it is working fine,
for delivery 4 it should show (1,2,3,4) but it is showing (2,3,4)
for delivery 6 it should show (1,2,3,4,5,6) but it is showing (5,6) and same for other numbers
Result:
Thanks
You need to create a separate table to associate WeekNo with the Deliveries Grouping
So you will have FactTable with Global_Key and WeekNo and another Table with a one to Many relationship with the Deliveries grouping
as below
You can do it in Two Ways
1)
FactTable:
Load GlobalKey,WeekNo
From SomeSourceTable;
WeekGroup:
Load
WeekNoFrom+iterno()-1 as WeekNo
,Deliveries
While WeekNoFrom+iterno()-1 <=WeekNoTo
;
load * inline [
WeekNoFrom,WeekNoTo,Deliveries
1,1,Last1Week
1,4,Last4Week
1,6,Last6Week
1,13,Last13Week
1,26,Last26Week
1,52,Last52Week
];
Concatenate(WeekGroup)
Load Distinct WeekNo,'ALL'
Resident FactTable;
Option 2:
FactTable:
Load GlobalKey,WeekNo
From SomeSourceTable;
WeekGroup:
Load Distinct WeekNo,'Last1Week' as Deliveries
Resident FactTable
Where WeekNo=1;
Concatenate(WeekGroup)
Load Distinct WeekNo,'Last4Week' as Deliveries
Resident FactTable
Where WeekNo<=4;
Concatenate(WeekGroup)
Load Distinct WeekNo,'Last6Week' as Deliveries
Resident FactTable
Where WeekNo<=6;
.
.and
.so
.on
.for each grouping
.
.
Concatenate(WeekGroup)
Load Distinct WeekNo,'ALL'
Resident FactTable;
Both the solutions are working fine.
Thanks a lot