topic Re: Create filter with continuous numbers in App Development

Create filter with continuous numbers

sandp707 — Tue, 06 Dec 2022 17:58:04 GMT

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

Re: Create filter with continuous numbers

BrunPierre — Tue, 06 Dec 2022 21:15:27 GMT

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

Re: Create filter with continuous numbers

sandp707 — Wed, 07 Dec 2022 06:41:50 GMT

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

Re: Create filter with continuous numbers

vinieme12 — Wed, 07 Dec 2022 08:11:31 GMT

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

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;

Re: Create filter with continuous numbers

sandp707 — Wed, 07 Dec 2022 19:13:43 GMT

Both the solutions are working fine.

Thanks a lot