Grouping, whilst excluding and limiting data

[jlampard40]

Hi guys.  I have the attached data (sample of).  I need to show the [AD_Req_Att_Days], but grouped as follows:

0-7 days
8-14 days
Over 14 days

I also only wish to show the demand element of this (see field [AD_Data]).

I also only want to show those values which are greater than zero (the minus figures are data quality issues with our upload from systems).  I also wish to exclude any '9999' numbers within the [AD_Req_Att_Days] field.

The field [AD_Event_Key] is a unique value which is generated by our systems - we use it when we're counting in Qlikview.  You'll notice there is often one of these event keys for each of activity and demand.  We only want to display those occurences relating to Activity (as explained above).

Preferably shown in a straight table.

I'd then like to create a list box which will allow me to select 0-7, 8-14 or Over 14 days.

Hopefully this is all possible!  Many thanks.

[dplr-rn]

use interval match 

e.g. below script

range:
load * Inline [
start, end,group
0,7,0-7 days
8,14,8-14 days
14,9998,Over 14 days


];
fact:
LOAD
    AD_Event_Key,
    AD_Data,
    AD_Req_Att_Days
FROM [lib://Downloads/20190925 Sample Excel QV.xlsx]
(ooxml, embedded labels, table is [Sample data]);



left Join IntervalMatch ( AD_Req_Att_Days )
load start,end
resident range;

// rest of the code is just to remove the synthetic key
left join(fact)
load 
start,end,group
Resident range;

drop table range;