Can I join on 2 autonumber fields(based on the same 2 fields) across 2/multiple tables
I am creating a composite key similar to FirstName &'|'& LastName across multiple tables. I am able to join these tables using this field. I wanted to use autonumber on it to make it more efficient. Doesn't seem to matter if I use AutoId or not it is numbering this field sequentially across tables. So the first table for that field I have 1 through 44, then the 2nd table has 45-153 and I assume because of this it is no longer able to join on this field. What am I missing/doing wrong?
Sorry I should have mentioned this is being done in the load script and I am using the AutoNumber function. My issue is:
Say on T1 I have FirstName = Dave and LastName = Smith. S Autonumber(FirstName&'|'&LastName, 'FirstLast') As 'FirstLast' would return a 1
Then on T2 I have the same First and Last name, so both T1 and T2 would look like Dave|Smith, but T1 autonumbers this as a 1 and T2 would autonumber it as a 2, or whatever the next number would be after all the autonumbers from T1 are done. I feel like the page you linked explains this as the correct behavior, but then I can't join the tables on these fields because the autonumbers differ right?
I understood that it's in script. I'm asking why you aren't using the AutoNumber script statement, rather than the AutoNumber() script function. It seems like it would solve your problem with less hassle? My understanding is that the script statement does the same thing, but it does so on the field's list of values, not based on a single table.
It is being applied in the same script, different section as each table is in its own section
t1: load F1, autonumber(F2 & '|' & F3) as F4 from X; --Wouldn't this produce a 1 from autonumber?
t2: load F5, autonumber(F2 & '|' & F3) as F4 from Y; --Wouldn't this produce a 2 from autonumber?
t3: load * resident t1; left join(t1) load * resident t2; --If the above is true how can it join on T1s autonumber field being a 1 and t2s autonumber field being a 2?
Oh I didn't realize that was a thing. I think I am using the script function. Heres a line from my load script
AutoNumber(FirstName &'|'& LastName, 'FirstLast') as FirstLast.
If I use that same line on another table it starts with the next number after autonumber is done with T1 fields. So the autonumbers for each table differ and don't join the 2 tables.
I did not build this app, I took it over from someone else. I actually think it can be done with a map instead as the joins only appear to be adding 1 column from another table to each table with the autonumber functions. I'm just curious why it's not working now when it was previously. There was a change to the datasource for some of these tables but I can join the tables based on FirstName|LastName without the autonumber so I don't think the datasource change is the cause of it not working.
Thats what I figured, as I mentioned in another post I just made, I am able to join these tables on the field FirstName|LastName so the strings should be the same across each table. Is there a way I can test this? I copied FirstName and LastName from 2 of the tables and put them in word and they matched there.
Does AutoId affect this? I thought if they all use the same AutoId that would ensure the autonumbers matched if the string values matched, but with or without AutoId its always sequential numbers not the same numbers across tables
You may try it by skipping the second parameter of the autonumber() and also to apply autonumber as script-statement instead as function.
Beside this you could add both information (without the join) to see which string get which number, like:
t: load *, F1 & '|' & F2 as F3, autonumber(F1 & '|' & F2) as F4 from X;
Like hinted above I doubt that you will see a significantly difference in the run-time and if there are really larger datasets then autonumberhash128() will being faster :
