topic Re: Dynamic mapping in QlikView

Dynamic mapping

galileumax — Wed, 05 Sep 2018 16:46:20 GMT

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

Re: Dynamic mapping

felipedl — Wed, 05 Sep 2018 17:21:51 GMT

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:

Re: Dynamic mapping

galileumax — Wed, 05 Sep 2018 18:02:23 GMT

Great!! It's perfect Thank you so much

Re: Dynamic mapping

felipedl — Wed, 05 Sep 2018 18:03:44 GMT

No problem.

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

Thanks.