Make a selection with Range

[danosoft]

Hi i have this situation:

i have 2 selectbox:

a. with the years from my DATE_INI_VAL

b. with month from my DATE_INI_VAL

and i have a TABLE where i show all my rows

when i click in my Month, i NOT want select the rows in my table of my Month.... but i want select all my rows in my table that are in this range:

DATE_INI_VAL <= month (selected)   AND   DATE_FIN_VAL >= month (selected)

how can i do that?

[danosoft]

Thanks you Youssef, but is impossible to work in this way 😞

When i open it with the AccessPoint it go on timeout.... and in the server it make 23 Gb Ram busy....

[YoussefBelloum · Accepted solution]

here is one last approach, it will create a synthetic key but no duplicate lines:

this one will not affect the performance at all.

data:

LOAD FORNITORE,

    [COD. CLIENTE],

    [RAGIONE SOCIALE],

    MIS_MENSILE,

    D_FINE_VAL,

    D_INIZ_VAL

FROM

(biff, embedded labels, table is Sheet1$);

MinMax:

Load

Min(D_INIZ_VAL) as MinDate,

Max(D_FINE_VAL) as MaxDate

Resident data;

Let vMin = FieldValue('MinDate',1);

Let vMax = FieldValue('MaxDate',1);

Let vDays = vMax-vMin+1;

Drop Table MinMax;

Calendar:

Load

Date($(vMin) + RowNo()-1) as Date

AutoGenerate ($(vDays));

left join

load Date,

  year(Date) as Year,

  month(Date) as Month,

    Date(MakeDate(year(Date),month(Date)),'YYYYMM')  as  YearMonth

resident Calendar;

RangeToDate:

intervalmatch (Date) load D_INIZ_VAL, D_FINE_VAL resident data;

just change the last code with all this and let me know.

[danosoft]

Wow, thanks Youssef, this is GREAT FAST!!!! Thanks... it solve!!

[YoussefBelloum]

You're Welcome Daniele !

It was a looong thread..Pleased to know your problem is solved

Good luck

[danosoft]

Say me please if i need to open another Discussion, or i can continue there Youseff, becouse i have another question about this kind of thing:

1. Is possible to separate Year to Month in the selection (have 2 selections separated)?

2. I want make another filter too, the filter need be to the field D_FINE_FORN (you not see it in the load example, but it exist now) i want the filter is if i select for example Month=3

D_FINE_FORN = Month+1

or

D_FINE_FORN = Month + 2

Thanks

[YoussefBelloum]

1. on the application with the script I gave you above, you have Date field, YearMonth field and also separated Month field and Year field.

just use a listbox and choose Year, use another listbox and choose Month.

2.I don't really understand what you want to do.. Is the field D_FINE_FORN a Month field ?

[danosoft]

1. ok thanks i understand

2. The D_FINE_FORN is a field with date, like the other field D_INIZ_VAL.

i want the filter in it; if i select for example Month=3

i want filter my table for:

D_FINE_FORN = Month+1

or

D_FINE_FORN = Month + 2

i need to do others filter like those, similar, how can i do that?

[danosoft]

for example..... 3rd filter i need to do is a filter like this:

D_INIZ_VAL = month selected

AND

D_INIZ_VAL > D_FIN_VAL

[YoussefBelloum]

Hi Daniele, I see that you opened a new ticket for this.. it is better. I was kind of stuck on this one..

Don't hesitate to TAG a specific person if you think that he/she can help

Good luck

[danosoft]

Hi Yuseef, is possible to put in your solution this kind of filter:

now is: DATE_FIN_VAL >= month (choose)

update in:  DATE_FIN_VAL >= Max of months i choose

is possible?