Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
hi
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
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.
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
hi
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
I see, yes this will work too 🙂
Thanks!