Dynamic mapping

[galileumax]

Hello to all,

it's possible to use mapping dynamically ?

From a table made like this :

Name	from	to	Group
pippo	01/01/2018	31/05/2018	Pink
pippo	01/06/2018	31/08/2018	Red
pippo	01/09/2018	31/10/2018	Yellow
pippo	01/11/2018	30/11/2018	Green
pippo	01/12/2018	31/12/2018	White
pluto	01/01/2018	31/05/2018	Red
pluto	01/06/2018	31/08/2018	Pink
pluto	01/09/2018	31/10/2018	Green
pluto	01/11/2018	30/11/2018	Yellow
pluto	01/12/2018	31/12/2018	White
paperino	01/01/2018	31/05/2018	White
paperino	01/06/2018	31/08/2018	Pink
paperino	01/09/2018	31/10/2018	Yellow
paperino	01/11/2018	30/11/2018	Green
paperino	01/12/2018	31/12/2018	Red

I would like to have a table like that:

ID	Name	Date	Pz	Group
1	pippo	13/05/2018	67	Pink
2	pluto	14/10/2018	53	Green
3	paperino	02/02/2018	22	White
4	pippo	01/06/2018	66	Red
5	pluto	24/12/2018	32	White
6	paperino	19/09/2018	8	Yellow
7	pippo	29/04/2018	77	Pink
8	pluto	27/11/2018	44	Yellow
9	paperino	08/12/2018	33	Red
10	pippo	12/05/2018	22	Pink
11	pluto	15/09/2018	33	Green
12	paperino	08/03/2018	55	White
13	pippo	09/11/2018	11	Green
14	pluto	23/08/2018	11	Pink
15	paperino	31/03/2018	33	White
16	pippo	14/01/2018	99	Pink
17	pluto	02/01/2018	76	Red
18	paperino	07/09/2018	56	Yellow
19	pippo	18/06/2018	54	Red
20	pluto	15/03/2018	12	Red
21	paperino	23/09/2018	10	Yellow

Assign the group in relation to the date range of the first table

Someone could help me.

Thanks in advance

[felipedl]

Hi Massimo,

You can use intervalmatch with more than one key (Date and Name fields) like so:

map:
load * Inline
[
Name,from,to,Group
pippo,01/01/2018,31/05/2018,Pink
pippo,01/06/2018,31/08/2018,Red
pippo,01/09/2018,31/10/2018,Yellow
pippo,01/11/2018,30/11/2018,Green
pippo,01/12/2018,31/12/2018,White
pluto,01/01/2018,31/05/2018,Red
pluto,01/06/2018,31/08/2018,Pink
pluto,01/09/2018,31/10/2018,Green
pluto,01/11/2018,30/11/2018,Yellow
pluto,01/12/2018,31/12/2018,White
paperino,01/01/2018,31/05/2018,White
paperino,01/06/2018,31/08/2018,Pink
paperino,01/09/2018,31/10/2018,Yellow
paperino,01/11/2018,30/11/2018,Green
paperino,01/12/2018,31/12/2018,Red
];

Data:
load * Inline
[
ID,Name,Date,Pz
1,pippo,13/05/2018,67
2,pluto,14/10/2018,53
3,paperino,02/02/2018,22
4,pippo,01/06/2018,66
5,pluto,24/12/2018,32
6,paperino,19/09/2018,8
7,pippo,29/04/2018,77
8,pluto,27/11/2018,44
9,paperino,08/12/2018,33
10,pippo,12/05/2018,22
11,pluto,15/09/2018,33
12,paperino,08/03/2018,55
13,pippo,09/11/2018,11
14,pluto,23/08/2018,11
15,paperino,31/03/2018,33
16,pippo,14/01/2018,99
17,pluto,02/01/2018,76
18,paperino,07/09/2018,56
19,pippo,18/06/2018,54
20,pluto,15/03/2018,12
21,paperino,23/09/2018,10
];

left join (Data)
IntervalMatch(Date,Name)
Load
from,
to,
Name
Resident map;

left join (Data)
Load
Name,
from,
to,
Group
Resident map;

drop table map;

drop fields from,to from Data;

This will get the result:

[galileumax · Accepted solution]

Great!! It's perfect Thank you so much

[felipedl]

No problem.

Can you mark the post as answered to help other too?

Thanks.