topic Re: [Left Join / Less Rows] Issue in QlikView

[Left Join / Less Rows] Issue

adamjank — Tue, 28 Mar 2017 07:52:51 GMT

Hello,

I've experienced very, very strange situation as the SQL experienced employee.
So after Left Joining two tables, I've got less rows on the output...
For many reasons it can be more rows due to duplicates etc., but it shouldn't be less rows.

What are the possible reasons and the solutions for that critical issue?

The keys are generated via Autonumber() and it has the same seed, furthermore I haven't got the NULLs on these fields.

I'll try to prepare the file and the script.

27-3-2017 16:41:31: 0030 Binary [..\XXX.qvw]

27-3-2017 16:41:51: 0031 Let vNumberOfRows = NoOfRows('FINAL')

27-3-2017 16:41:51: 0032 Trace 4877810

27-3-2017 16:41:51: 0032 4877810

27-3-2017 16:41:51: 0033 Left JOIN(FINAL)

27-3-2017 16:41:51: 0034 LOAD Distinct * RESIDENT D_XXX

27-3-2017 16:41:51: 11 fields found: %XXX.KEY, XXX.Name 4 516 lines fetched

27-3-2017 16:41:51: Joining/Keeping

27-3-2017 16:42:08: 0035 Drop Table D_XXX

27-3-2017 16:42:09: 0036 Let vNumberOfRows = NoOfRows('FINAL')

27-3-2017 16:42:09: 0037 Trace 4219022

27-3-2017 16:42:09: 0037 4219022

Kind Regards,
Adam

Re: [Left Join / Less Rows] Issue

sunny_talwar — Tue, 28 Mar 2017 08:54:56 GMT

27-3-2017 16:41:31: 0030 Binary [..\XXX.qvw]

27-3-2017 16:41:51: 0031 Let vNumberOfRows = NoOfRows('FINAL')

27-3-2017 16:41:51: 0032 Trace 4877810

27-3-2017 16:41:51: 0032 4877810

Do you have a where statement here? 0032?

Re: [Left Join / Less Rows] Issue

adamjank — Tue, 28 Mar 2017 08:56:26 GMT

It's just a plain binary load without any 'where', additional conditions.

Re: [Left Join / Less Rows] Issue

sunny_talwar — Tue, 28 Mar 2017 08:57:35 GMT

Would you be able to share the script

Re: [Left Join / Less Rows] Issue

adamjank — Tue, 28 Mar 2017 09:01:06 GMT

Binary [..\XXX.qvw];

Let vNumberOfRows = NoOfRows('FINAL');

Trace $(vNumberOfRows);

Left JOIN(FINAL)

LOAD Distinct * RESIDENT D_XXX;

Drop Table D_XXX;

Let vNumberOfRows = NoOfRows('FINAL');

Trace $(vNumberOfRows);

Re: [Left Join / Less Rows] Issue

sunny_talwar — Tue, 28 Mar 2017 09:03:40 GMT

Ya that is weird... may be pcammaert‌ can offer his expert advice here

Re: [Left Join / Less Rows] Issue

jonathandienst — Tue, 28 Mar 2017 09:23:04 GMT

I think this is due to some quirky behaviour in the way QV handles DISTINCT. If you join with a distinct table, the DISTINCT operation propagates upward to the first table, so it de-duplicates the first table, reducing the number of rows.

To avoid this, assuming you need the DISTINCT, you may need to distinct load the second table into a resident table, and then left join the resident table to the binary loaded table.

Re: [Left Join / Less Rows] Issue

adamjank — Tue, 28 Mar 2017 09:30:21 GMT

Binary [..\XXX.qvw];

Let vNumberOfRows = NoOfRows('FINAL');

Trace $(vNumberOfRows);

Left JOIN(FINAL)

LOAD *;

LOAD DISTINCT * RESIDENT D_XXX;

Drop Table D_XXX;

Let vNumberOfRows = NoOfRows('FINAL');

Trace $(vNumberOfRows);

Solution, so the DISTINCT does not affect the first table.

THANK YOU A LOT jontydkpi‌

Re: [Left Join / Less Rows] Issue

Peter_Cammaert — Tue, 28 Mar 2017 09:48:36 GMT

Indeed.

Funny that a Preceding Load apparently also blocks the DISTINCT propagation. Should check this out.

Re: [Left Join / Less Rows] Issue

Peter_Cammaert — Tue, 28 Mar 2017 10:03:14 GMT

This is getting weird. In Adams last post, the DISTINCT keyword doesn't work at all, not even in the 2nd table (the one that is loaded first). How crazy can this become?

For example, how many rows do you get in the final copy of the RawData table?

RawData:

LOAD * INLINE [

Prod, Cust, Items

A, C1, 5

B, C1, 3

C, C2, 10

C, C3, 5

A, C1, 5

B, C1, 3

];

LET vNumRows = NoOfRows('RawData');

TRACE >>>>> RawData before JOIN: $(vNumRows) rows;

LEFT JOIN (RawData) LOAD *;

LOAD DISTINCT * INLINE [

Prod, ProdName

A, Food

B, Brick

C, Chair

B, Brick

A, Food

];

LET vNumRows = NoOfRows('RawData');

TRACE >>>>> RawData after JOIN: $(vNumRows) rows;

Re: [Left Join / Less Rows] Issue

jonathandienst — Tue, 28 Mar 2017 10:23:37 GMT

Yes - it seems that it is an all or nothing thing.

Your sample script with the preceding load - DISTINCT is ignored:

And without the preceding load - DISTINCT propagates:

Re: [Left Join / Less Rows] Issue

Peter_Cammaert — Tue, 28 Mar 2017 10:27:43 GMT

Exactly.

I knew that your suggestion to use a RESIDENT table was the more reliable one. It still stands.

At least you got 10 virtual points from me