topic Re: Joining of two tables using AS in QlikView

Joining of two tables using AS

Thu, 21 Aug 2014 10:15:13 GMT

Hi!

I am having two tables Table A and Table B

Table A have field 'Name' and table B having 'Name1', Count of 'Name' in Table A is 1000(example)

and Count of 'Name1' in Table B is 500(example).Both 'Name' and 'Name1' are having some common fields,

when I written 'Name1 as Name' association occured and two tables linked,

but the question is number is different when I change loading order of tables i.e., when I load first Table A then Table B it showing one value of count(Name) and if I load Table B then Table A it showing different value of count(Name).

Thanks.

Re: Joining of two tables using AS

Gysbert_Wassenaar — Thu, 21 Aug 2014 10:20:38 GMT

If you use Name as the field to associate the tables, i.e. as a key field, then you shouldn't use it in the count function. That's because Qlikview cannot know from which table you want the count. Use another field to calculate the count. If you want the total distinct count of Name over all tables you can use FieldValueCount('Name') instead.

Re: Joining of two tables using AS

Thu, 21 Aug 2014 10:32:13 GMT

Hi,

I will write scenario here

TableA:

load

Name

from abc.qvd;

TableB:

load

Name1 as Name

from xyz.qvd;

TableB:

load

Name1 as Name

from xyz.qvd;

TableA:

load

Name

from abc.qvd;

does 1 and 2 are same or not, please explain.

Re: Joining of two tables using AS

Gysbert_Wassenaar — Thu, 21 Aug 2014 10:38:01 GMT

In the example you posted you will end up with only one table with only one field: Name. If two tables have exactly the same fields the data will be concatenated into one table. So in your example 1. and 2. will have the same result.

Re: Joining of two tables using AS

Thu, 21 Aug 2014 10:47:45 GMT

I interpreted wrongly in above example, please see the below one

TableA:

load

Name,

city,

state,

country

from abc.qvd;

TableB:

load

Name1 as Name,

pin,

phone,

street

from xyz.qvd;

TableB:

load

Name1 as Name,

pin,

phone,

street

from xyz.qvd;

TableA:

load

Name,

city,

state,

country

from abc.qvd;

does 1 and 2 are same or not, please explain.

Re: Joining of two tables using AS

Gysbert_Wassenaar — Thu, 21 Aug 2014 10:57:14 GMT

1. and 2. both load the same data. But you should NOT, repeat NOT!!!!, use a key field in the count function. So DO NOT USE the field Name in the count function. Qlikview CANNOT know from which table you want the count. Therefore the behaviour of the count function over a key field is UNDEFINED. Meaning anything can happen. Your car may pee on your cat. Or your fridge may take your front door to the movies. DO NOT USE a key field in the count function!!!