topic Re: Concatenation / Null Values where a key exists in QlikView

Concatenation / Null Values where a key exists

Wed, 15 Jul 2015 07:41:50 GMT

Hello,

I am trying to concatenate a table into my master table where there are several key fields that are in common in both tables.

When I concatenate them, there appears to be no link in those fields ie:

Master:

Table: ** The table I want to be concatenated in

Master: ** this is the result after the concatenation:

What I want to happen is:

I have tried other joins of the table ie. outer join / join with no success as well as trim around the Code which is one of the links.

Any help is much appreciated - can't quite work out why this is not working as I would imagine it to...

Thanks

Re: Concatenation / Null Values where a key exists

Kushal_Chawda — Wed, 15 Jul 2015 07:47:39 GMT

Instead of Concatenate do left Join with master table. for eg,

Master:

LOAD * INLINE [

Fruit, Code

Apple,1111111

Mago, 2222222

Banana,333333

Peach,444444

Strawberry,55555

];

Left Join

LOAD * INLINE [

OtherData, Code

Spain,1111111

France, 2222222

Belgium,333333

USA,444444

Brazil,55555

];

Re: Concatenation / Null Values where a key exists

sasiparupudi1 — Wed, 15 Jul 2015 07:52:24 GMT

May be you have synthetic keys in the table..

Re: Concatenation / Null Values where a key exists

Wed, 15 Jul 2015 07:55:21 GMT

Correct, I do.... is this an issue?

Re: Concatenation / Null Values where a key exists

jonathandienst — Wed, 15 Jul 2015 07:55:53 GMT

Kush is correct - you need a join.

Simply put:

Concatenate generally adds extra rows

Join is generally adds extra columns to the existing rows

Re: Concatenation / Null Values where a key exists

jagan — Wed, 15 Jul 2015 08:08:46 GMT

Hi,

Instead of Concatenation you need to Join both the tables in this scenario, so that you will get the required ouput.

Regards,

Jagan.

Re: Concatenation / Null Values where a key exists

sasiparupudi1 — Wed, 15 Jul 2015 08:15:14 GMT

Your join should work fine if you resolve synthetic keys in your data model

Re: Concatenation / Null Values where a key exists

Wed, 15 Jul 2015 08:17:00 GMT

On a separate note to the above example but still related to concatenating and joining:

When I use a join I lose the data

When I concatenate I get Null values

and when I have a synthetic key I am able to calculate correctly...

Re: Concatenation / Null Values where a key exists

Wed, 15 Jul 2015 08:19:16 GMT

How is best to resolve a synthetic key when there are 3 key fields that are in common in both tables.

When I join, the data is not correct

When I concatenate I get null values

Re: Concatenation / Null Values where a key exists

sasiparupudi1 — Wed, 15 Jul 2015 08:22:51 GMT

You could may be use a concatenated key between the tables

key1 &'||'& key2 &'||'&key3 as NewKey

Re: Concatenation / Null Values where a key exists

martinpohl — Wed, 15 Jul 2015 08:23:24 GMT

a join is done by all fields within the same name, like a link in your data model between tables.

So if you want to do a join by one field you need to load only the linking field and all fields you want to add to your table (in your example

join load

Code,

[Other Data}

from source;

Regards

Re: Concatenation / Null Values where a key exists

Kushal_Chawda — Wed, 15 Jul 2015 09:13:05 GMT

Do the join on Key field. In your case would be Code, rename other fields with same name to avoid synthetic key

Re: Concatenation / Null Values where a key exists

prashantbaste — Wed, 15 Jul 2015 09:27:22 GMT

Hi there,

1. You should remove Synthetic keys as said by Sasidhar - key1 &'||'& key2 &'||'&key3 as NewKey

and alias your original field-name with new names to avoid synthetic keys.

2. Join will help to resolve your issue & obtain desired output.

Re: Concatenation / Null Values where a key exists

Wed, 15 Jul 2015 13:06:34 GMT

Creating the key works in terms of getting the correct data without the synthetic keys.

However when I then try to join the table I lose the data.

So currently I have a snowflake data structure with the new key as the reference between the data tables