topic Re: Can someone please explain this behaviour? in QlikView

Can someone please explain this behaviour?

Thu, 01 Sep 2016 11:08:00 GMT

Hi Everyone

I am loading two tables in Qlikview and I getting two strange results.

1 - When performing a left join I get "tablename-1". See image below.

2 - When perfomring a left join I am getting the same number of records.

Below I have posted the script execution dialog and the script itself. Can someone shed some light on the issue? if there is an issue at all

Re: Can someone please explain this behaviour?

Anil_Babu_Samineni — Thu, 01 Sep 2016 15:17:30 GMT

What is the issue you area facing

Re: Can someone please explain this behaviour?

Fri, 02 Sep 2016 08:55:06 GMT

Hi Anil

Please see the question stem:

1 - When performing a left join I get "tablename-1". See image below.

2 - When perfomring a left join I am getting the same number of records.

Re: Can someone please explain this behaviour?

tracysmart — Fri, 02 Sep 2016 09:07:23 GMT

you are getting the tablename-1 as you don't name the table in the script but this is usual behaviour and nothing to worry about as you are joining the table. If you didn't join it, it would be named that.

As for getting the same number of records - your join is a one to many so when you join the records from Finance_OrderItemHeader.qvd in the final load, you are loading the many rows from that table as they have the same %Key_ItemNumber. This is the same table you load at the top and then drop.

What are you trying to achieve here? If you want distinct rows, then ensure you use the Distinct keyword.

Re: Can someone please explain this behaviour?

Fri, 02 Sep 2016 10:29:39 GMT

Hi Tracy

Thanks for your reply. Very helpful. I now understand why the table is called tablename-1.

Regarding the second part of my query I ran a test with dummy data and it turns out its normal behaviour but in my opinion very misleading. When qlikview performed the left join I was expecting to get the only the number of rows that matched not the number of rows of the entire table.

I ran a quick test with inline tables please see below. I left join a table and for that task in the dialog box I get the entire number of records not only the number of records that were joined. In this case it only joined 5 records but it still showed 10 records fetched.

//=========================================================================================

// LOAD ORDER ITEM HEADER

//=========================================================================================

Order_item_header:

LOAD * INLINE [

    key_itemnumber, value1

    1, a

    2, b

    3, c

    4, d

    5, e

    6, f

    7, g

    8, h

    9, i

    10, j

];

//=========================================================================================

// LOAD EXCHANGE RATE

//=========================================================================================

Exchange_rate:

LOAD * INLINE [

    key_pblid, key_itemnumber, value2

    1,1, a

    2,2, b

    3,3, c

    4,4, d

    5,5, e

6,11,k

7,12,l

]

WHERE EXISTS(key_itemnumber);

//=========================================================================================

// DROP TABLE

//=========================================================================================

DROP TABLE Order_item_header;

//=========================================================================================

// LEFT JOIN ORDER ITEM HEADER TO EXCHANGE RATE

//=========================================================================================

LEFT JOIN(Exchange_rate)

LOAD * INLINE [

key_itemnumber, value1

1, a

2, b

3, c

4, d

5, e

6, f

7, g

8, h

9, i

10, j

];