topic Re: why does this give me 4 rows? i'm expecting 2 in QlikView

why does this give me 4 rows? i'm expecting 2

amien — Thu, 20 Oct 2011 16:49:14 GMT

Why does this give me 4 rows in the table? i'm expecting only 2 rows.

DateRange:
LOAD * INLINE [
StandDate
40833
];
StatusRanges:
LOAD * INLINE [
    seqnr, MoveSeqnr, MoveDateStart, MoveDateEnd
    1    , 1        , 40833           , 40833
    1    , 2        , 40833        , 40833
];
LEFT JOIN (StatusRanges) 
INTERVALMATCH (StandDate) LOAD MoveDateStart, MoveDateEnd RESIDENT StatusRanges;
DROP TABLE DateRange;

And when i do this instead (only difference is a DISTINCT, while there is nothing to DISTINCT!)

StatusRanges:

LOAD DISTINCT * INLINE [

seqnr, MoveSeqnr, MoveDateStart, MoveDateEnd

1 , 1 , 40833 , 40833

1 , 2 , 40833 , 40833

];

This gives me two rows in the table. Like it should be

Seems like the DISTINCT also influence the LEFT JOIN that comes after that..

Can someone explain this?

Re: why does this give me 4 rows? i'm expecting 2

Miguel_Angel_Baeyens — Thu, 20 Oct 2011 20:15:06 GMT

Hello Amien,

The following should work

DateRange:
LOAD * INLINE [
StandDate
40833
];

StatusRanges:
LOAD * INLINE [
    seqnr, MoveSeqnr, MoveDateStart, MoveDateEnd
    1    , 1        , 40833        , 40833
    1    , 2        , 40833        , 40833
];

DatesWithRanges:
INTERVALMATCH (StandDate) LOAD MoveDateStart, MoveDateEnd RESIDENT StatusRanges;
JOIN LOAD * RESIDENT DateRange;
JOIN LOAD * RESIDENT StatusRanges;

DROP TABLES DateRange, StatusRanges;

I'm labelling the table resulting from the IntervalMatch() load and then joining the values from the two precedent tables. My guess on why you are getting four rows is that the IntervalMatch that returns two lines is LEFT JOINing with the StatusRanges table that already has two lines, twice the 1 MoveSeqnr with StandDate and without it, twice the 2 MoveSeqnr likewise, both having the same range.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

why does this give me 4 rows? i'm expecting 2

amien — Mon, 24 Oct 2011 08:35:11 GMT

My guess on why you are getting four rows is that the IntervalMatch that returns two lines is LEFT JOINing with the StatusRanges table that already has two lines, twice the 1 MoveSeqnr with StandDate and without it, twice the 2 MoveSeqnr likewise, both having the same range.

Ok .. i can understand this .. but why does DISTINCT solve my problem, while there is nothing to DISTINCT. The table is the same with or without the DISTINCT.

How does Qlikview Join? is that join row for row during reloading .. or first completly load the second table and than join?

Re: why does this give me 4 rows? i'm expecting 2

Miguel_Angel_Baeyens — Mon, 24 Oct 2011 10:06:19 GMT

Hi Amien,

The intervalmatch in your code above returns only the date and the possible ranges, that are two. DISTINCT will actually return only one line, instead of two rows (start date, end date, date) so my guess was based on the distinct on the intervatmach rather than the table alone, that indeed, only has different rows.

Why? Because for the interval you only take two fields, and these fields are in two identical rows. I haven't tried it, but probably

INTERVALMATCH (StandDate) LOAD DISTINCT MoveDateStart, MoveDateEnd RESIDENT StatusRanges;

in your code will return two rows instead of one. These are the fields that make up the composite key to link with the StatusRanges table, and in this join is where the distinct takes place.

And perhaps my thought is based on a buggy behavior of mixing intervalmatch, join, load resident... and how QlikView actually interprets the code (theoretically, line by line, top down). Honestly, I'm not in a position to say positive how does QlikView join.

My experience (read, what I've seen) leads me to think that first loads completely each of the tables, then join them. This makes sense since you only LOAD when the FROM, SQL SELECT or RESIDENT read has been completed (meaning when reading from a database with a join, it only reads when the join is complete). And even more lately, watching QlikView burst the memory usage of memory and eventually hung trying to do three or four consecutive joins for some thousands of rows (that I wrongly thought it was a cakewalk).

There are some posts about this by John Witherspoon and Rob Wunderlich here that can shed some light on this. Thare may be some resources in the PRM Librares as well...

Regards.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: why does this give me 4 rows? i'm expecting 2

amien — Mon, 24 Oct 2011 10:16:14 GMT

Yes .. but my DISTINCT is in the Inline Script (StatusRanges table) .. i have moveseqnr there .. and with or without DISTINCT, there will always be two rows.

i don't see how my DISTINCT in the Inline script if of influence of the intervalmatch that comes after that.

i think i will leave it like this .. it works, i don't understand it .. could be a bug or some qlikview core intel that we don't know of.

Thanks for your time

Or perhaps John can shine some light