IntervalMatch until Current Month

[microwin88x]

Hello,

I have the following table with different ID:

- Each ID has TYPE = Z010 (Meters) and TYPE = Z070 (Sales_%).

- Note that START_DATE and END_DATE have format = DD/MM/YYYY.

- The END_DATE could be 31/12/9999. This means the current VALUE is that one, but it could change in the future. If it changes today, then the value will set to 20/04/2018 and the database will automatically generate a new row with START_DATE = 21/04/2018 to END_DATE = 31/12/9999.

ID	TYPE	KEY	VALUE	START_DATE	END_DATE	MEASURE
IM001	Z010	IM001|Z010	34,36	01/01/2018	28/02/2018	METERS
IM001	Z010	IM001|Z010	64,28	01/03/2018	31/12/9999	METERS
IM001	Z070	IM001|Z070	0,7488	01/01/2018	28/02/2018	SALES_%
IM001	Z070	IM001|Z070	1,1741	01/03/2018	31/12/9999	SALES_%
IM002	Z010	IM002|Z010	60	01/01/2018	31/03/2018	METERS
IM002	Z010	IM002|Z010	73,29	01/04/2018	31/12/9999	METERS
IM002	Z070	IM002|Z070	0,7648	01/01/2018	31/03/2018	SALES_%
IM002	Z070	IM002|Z070	1,0556	01/04/2018	31/12/9999	SALES_%
IM003	Z010	IM003|Z010	46,27	01/01/2018	31/03/2018	METERS
IM003	Z010	IM003|Z010	93,58	01/04/2018	31/12/9999	METERS
IM003	Z070	IM003|Z070	0,6516	01/01/2018	31/03/2018	SALES_%
IM003	Z070	IM003|Z070	1,4053	01/04/2018	31/12/9999	SALES_%
IM004	Z010	IM004|Z010	323,55	01/01/2018	09/05/2018	METERS
IM004	Z010	IM004|Z010	273,61	10/05/2018	31/12/9999	METERS
IM004	Z070	IM004|Z070	1,2494	01/01/2018	09/05/2018	SALES_%
IM004	Z070	IM004|Z070	1,0393	10/05/2018	31/12/9999	SALES_%

What I need is to generate a new field PERIOD with the associated VALUE for Meters and Sales_%:

- The maximum PERIOD should be the current month, in this case, 201804.

ID	TYPE	KEY	VALUE	MEASURE	PERIOD
IM001	Z010	IM001|Z010	34,36	METERS	201801
IM001	Z010	IM001|Z010	34,36	METERS	201802
IM001	Z010	IM001|Z010	64,28	METERS	201803
IM001	Z010	IM001|Z010	64,28	METERS	201804
IM001	Z070	IM001|Z070	0,7488	SALES_%	201801
IM001	Z070	IM001|Z070	0,7488	SALES_%	201802
IM001	Z070	IM001|Z070	1,1741	SALES_%	201803
IM001	Z070	IM001|Z070	1,1741	SALES_%	201804
IM002	Z010	IM002|Z010	60	METERS	201801
IM002	Z010	IM002|Z010	60	METERS	201802
IM002	Z010	IM002|Z010	60	METERS	201803
IM002	Z010	IM002|Z010	73,29	METERS	201804
IM002	Z070	IM002|Z070	0,7648	SALES_%	201801
IM002	Z070	IM002|Z070	0,7648	SALES_%	201802
IM002	Z070	IM002|Z070	0,7648	SALES_%	201803
IM002	Z070	IM002|Z070	1,0556	SALES_%	201804
IM003	Z010	IM003|Z010	46,27	METERS	201801
IM003	Z010	IM003|Z010	46,27	METERS	201802
IM003	Z010	IM003|Z010	46,27	METERS	201803
IM003	Z010	IM003|Z010	93,58	METERS	201804
IM003	Z070	IM003|Z070	0,6516	SALES_%	201801
IM003	Z070	IM003|Z070	0,6516	SALES_%	201802
IM003	Z070	IM003|Z070	0,6516	SALES_%	201803
IM003	Z070	IM003|Z070	1,4053	SALES_%	201804
IM004	Z010	IM004|Z010	323,55	METERS	201801
IM004	Z010	IM004|Z010	323,55	METERS	201802
IM004	Z010	IM004|Z010	323,55	METERS	201803
IM004	Z010	IM004|Z010	323,55	METERS	201804
IM004	Z070	IM004|Z070	1,2494	SALES_%	201801
IM004	Z070	IM004|Z070	1,2494	SALES_%	201802
IM004	Z070	IM004|Z070	1,2494	SALES_%	201803
IM004	Z070	IM004|Z070	1,2494	SALES_%	201804

Do you know how could I get that?

Thank you!!!

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD *,

Date(MonthStart(START_DATE, IterNo()-1), 'YYYYMM') as PERIOD

While MonthStart(START_DATE, IterNo()-1) < RangeMin(MonthStart(Today(), 1), MonthStart(END_DATE, 1));

LOAD * INLINE [

    ID, TYPE, KEY, VALUE, START_DATE, END_DATE, MEASURE

    IM001, Z010, IM001|Z010, "34,36", 01/01/2018, 28/02/2018, METERS

    IM001, Z010, IM001|Z010, "64,28", 01/03/2018, 31/12/9999, METERS

    IM001, Z070, IM001|Z070, "0,7488", 01/01/2018, 28/02/2018, SALES_%

    IM001, Z070, IM001|Z070, "1,1741", 01/03/2018, 31/12/9999, SALES_%

    IM002, Z010, IM002|Z010, 60, 01/01/2018, 31/03/2018, METERS

    IM002, Z010, IM002|Z010, "73,29", 01/04/2018, 31/12/9999, METERS

    IM002, Z070, IM002|Z070, "0,7648", 01/01/2018, 31/03/2018, SALES_%

    IM002, Z070, IM002|Z070, "1,0556", 01/04/2018, 31/12/9999, SALES_%

    IM003, Z010, IM003|Z010, "46,27", 01/01/2018, 31/03/2018, METERS

    IM003, Z010, IM003|Z010, "93,58", 01/04/2018, 31/12/9999, METERS

    IM003, Z070, IM003|Z070, "0,6516", 01/01/2018, 31/03/2018, SALES_%

    IM003, Z070, IM003|Z070, "1,4053", 01/04/2018, 31/12/9999, SALES_%

    IM004, Z010, IM004|Z010, "323,55", 01/01/2018, 09/05/2018, METERS

    IM004, Z010, IM004|Z010, "273,61", 10/05/2018, 31/12/9999, METERS

    IM004, Z070, IM004|Z070, "1,2494", 01/01/2018, 09/05/2018, SALES_%

    IM004, Z070, IM004|Z070, "1,0393", 10/05/2018, 31/12/9999, SALES_%

];

[microwin88x]

Thank you Sunny! It worked