topic intervalmatch duplication of rows in QlikView

intervalmatch duplication of rows

Wed, 18 May 2011 10:32:03 GMT

Hi everybody,

here is an extremely simplified version of my script:

agende:
LOAD * INLINE [
    COD_EROG, DATAJ_INIZIO, DATAJ_FINE
    E1, 03/01/2011, 10/01/2011
    E2, 03/01/2011, 10/01/2011    
];
calendar:
LOAD * INLINE [
          DAY
          07/01/2011
];
match:
left join(agende)
IntervalMatch (DAY) LOAD DATAJ_INIZIO, DATAJ_FINE RESIDENT agende;

The problem is that doing this way agenda rows are duplicated. I have already read other post discussing alternative ways to do the same job and to avoid duplications. But I still haven't understood why rows are duplicated (sorry, maybe it is simple, forgive me if I'm a little dumb...).

Can someone explain me why intervalmatch creates the duplication (or point me to an already made explanation)?

Thanks in advance,

Fil

intervalmatch duplication of rows

erichshiino — Wed, 18 May 2011 12:18:26 GMT

Hi, Fil

I believe the duplication of your records is expected because the date 7/01 is in the middle of the line with E1 and E2. The date on calendar is associated with both intervals because they are the same.

I don't know what your data mean but the intervals should not overlap if you don't want duplicates.

Hope it helps,

Erich

intervalmatch duplication of rows

Wed, 18 May 2011 12:35:07 GMT

Hi Erich,

I would have expected to get a table like:

COD_EROG DATAJ_INIZIO DATAJ_FINE DAY

E1 03/01/2011 10/01/2011 07/01/2011

E2 03/01/2011 10/01/2011 07/01/2011

while I get:

COD_EROG DATAJ_INIZIO DATAJ_FINE DAY

E1 03/01/2011 10/01/2011 07/01/2011

E2 03/01/2011 10/01/2011 07/01/2011

Why is like that?

Thanks

intervalmatch duplication of rows

erichshiino — Wed, 18 May 2011 12:58:46 GMT

hI,

I used this now:

agende:

LOAD * INLINE [

COD_EROG, DATAJ_INIZIO, DATAJ_FINE

E1, 03/01/2011, 10/01/2011

E2, 03/01/2011, 10/01/2011

];

calendar:

LOAD * INLINE [

DAY

07/01/2011

];

match:

left join(agende)

IntervalMatch (DAY) LOAD DISTINCT DATAJ_INIZIO, DATAJ_FINE RESIDENT agende;

I included distinct because during interval match cod_erog was not considered. Your date was associated with the same interval and you got two lines. Then when you use the left join (with two identical lines) you duplicated it again and get two for each code.

Hope it helps,

Erich

intervalmatch duplication of rows

Wed, 18 May 2011 13:04:53 GMT

Thank you, I had already discovered that using distinct the problem would be solved. But I hadn't understood exactly why, without, distinct, the rows were duplicated.

Now your reply has enlighted me! Sometimes, just speaking with some else helps to understand...

Thanks,

Fil