auto number

[Luben]

how do i use auto number to  join two or more tables 

[zwilson_borg · Accepted solution]

It looks like you're trying to use autonumber function to create a single composite key between two tables.  for the basic autonumber function you will want to join the relevant fields into a single string as the parameter. For example, if you wanted to use a user's name and job title it would be: autnumber([first]&[last]&[job title]). However there are times where data values could collide. I would recommend using autonumberhash128 or autonumberhash256 where you can pass each data field as an individual argument. Looking at the earlier example autonumberhash128([first], [last], [job title]). Notice these are separated arguments as opposed to concatenating the string.

An example of when this could be a problem is if you had two employees called kat evans and kate vans, the joined string would be katevans in both cases. You could also add some sort of delimiter to solve this, but I think the autnumberhash functions are more robust solutions for you.

Finally, don't forget to either qualify, rename, or exclude the fields used for the composite key so that Qlik doesn't try to join on both the composite key and the fields.

[Luben]

Thank  you so much for your assistance.

[Luben]

so for this example is it possible to use autonumberhash128 to link both tables

by creating a key.

Thanks in advance.

Lu

Travel:

load 

point_from,

point_to

from Xyz.qvd;

coast:

load

point,

M_coast

From abc.qvd;

[zwilson_borg]

So this problem really doesn't lend itself to autonumberhash which is more designed for a situation where you making a composite key. I believe in this case you are looking to join both point_from and point_to. This is more akin to a canonical calendar where multiple fields are joined against a single calendar (in this case coast).  I would suggest making an intermediate table that serves as a bridge between them.

Travel:

Load

rowno() as id

point_from,

point_to,

from xyz.qvd;

Travel_Map:

Load

id,

point_from as point,

'from' as travel_event

Resident Travel;

Concatenate Load

id,

point_to as point,

'to' as travel_event

Resident Travel;

coast:

load

point,

M_coast

From abc.qvd;

This should create a scenario where you select an M_coast and get all travel events that go to or from this location. Additionally, you can use the newly created travel_event field to specify if you want events that only went to or from a coast. This can be conceptually a bit challenging so feel free to reach out if you have any questions.