topic Associating 2 fields with different names in QlikView

Associating 2 fields with different names

Fri, 04 Dec 2009 14:31:00 GMT

Hi,

I want to appolagise for my bad English, but it is not my first language.

I want to ask how to associate 2 fields from two different tables without changing their names to be the same. Lets say I have a table with [Customer Number] field and another table with [Customer ID] field. I want to associate those 2 fields during the reload, but without having to change the fields names. I've searched the forums and have read the manual and as far as I understood what's written I have to use autonumber() or autonumberhash128() function, but I'm a bit confused about that.

Please advise!

Best,

Maya

Associating 2 fields with different names

Fri, 04 Dec 2009 14:43:05 GMT

if you dont want to rename the fields, you can make a key

Load a1,b1,c1 from .....;

load a2,b2,c2 from ......;

now you want to join these two and want to keep that common field also

try this one

load a1 as key, a1,b1,c1 from ......;

load a2 as key, a2,b2,c2 from ......;

regards

Peter

Associating 2 fields with different names

Anonymous — Fri, 04 Dec 2009 14:44:15 GMT

Hi Maya,

We all speak different languages. No need to apologize.

Basically QlikView only associates fields with identical names. Is there a reason why you don't want to change the name?

Autonumber is used for generating a numeric value out of a field value and the same number will be generated for the same value in another field where autonumber is applied so that you get a connection, but the fields still have to be named the same.

If you have Customer Number in one table and Customer ID in another and want to keep them separate while still linking the tables, the easiest approach is to create a second field in one of the tables, for example:

Load
[Customer Number],
[Customer Number] as [Customer ID]
From xxx

That way you'll still have the Customer number in the table but also create a link with the other table containing Customer ID. Hope this makes sense.

Associating 2 fields with different names

Fri, 04 Dec 2009 15:16:56 GMT

Hi csavgssc and Johannes,

Thank you for your quick replies.

@csavgssc
Only in one of the tables the field will be a key, and actually I don't see how your suggestion will work...

@Johannes
Yes, there is a reason for me wanting this...
I started a new job as a BI in a big company and I inherited few QlikView applications that I should take care of. These applications are huge, lots of tables are loaded... So the previous developer, who created them developed a strategy for naming the fields and it goes like this:

[TN: Field name] - where TN are the initials of the Table Name.

So when there are 2 fields that should be associated they have different names, because they are from different tabels.

In his application I see he managed to do such an association, and now I see he did just what you are suggesting; and he uses autonumberhash128() to generate a key field and now I understand the logic behind the script thanks to you.

Best,
Maya

Associating 2 fields with different names

Fri, 04 Dec 2009 16:17:31 GMT

basically the idea is to make two fields in two tables with the same name in order to make a join between them. so it can be either way

1. a1 as key, a2 as key-----------here the common field is key

2. a1 as a2--------------here the common field is a2

regards

Peter

Associating 2 fields with different names

Fri, 04 Dec 2009 16:47:09 GMT

I see, yes this will work too 🙂

Thanks!