Autonumber() confusion, Using Autonumber in multiple tables

[shyamcharan]

Hi Experts,

Can I use Autonumber() function on multiple tables in the same load?

I have created a similar scenario that I have in my design. In this example, I have three tables Table1, Table2 and Table3.

I have used Autonumber() function on all three tables to generate unique key numbers.

As shown in the example, why I am not able to see values under c1 or c2 when i select c5=Northern Place?

Please view attached example.

I appreciate any help on this. Thanks in advance.

Regards,

Shyam.

[sunny_talwar · Accepted solution]

Try this:

OTD1:

LOAD * INLINE [

    O1, O2

    A123, Westmead

    B123, Parramatta

    C123, Central

];

Table1:

Load AutoNumber(O1) as C1, O2 AS C2

Resident OTD1;

//Drop Table TD1;

OTD2:

LOAD * INLINE [

    O1, O4

    A123, NorthSydney

    E123, Townhall

    F123, Katumba

];

T2:

Load AutoNumber(O1) as C1, AutoNumber(O4) as C4

Resident OTD2;

//Drop Table TD2;

OTD3:

LOAD * INLINE [

    O5, O4

    Northern place, NorthSydney

    City CBD, Townhall

    Rural Region, Katumba

];

T3:

Load AutoNumber(O4) as C4, O5 AS C5

Resident OTD3;

//Drop Table TD3;

[jagan]

Hi,

You need to use AutoNumber() for O4 in T2, you have to convert the columns in both the tables, otherwise one will be in numbers and another will be in Text.

OTD1:

LOAD * INLINE [

    O1, O2

    A123, Westmead

    B123, Parramatta

    C123, Central

];

Table1:

Load AutoNumber(O1) as C1, O2 AS C2

Resident OTD1;

//Drop Table TD1;

OTD2:

LOAD * INLINE [

    O1, O4

    A123, NorthSydney

    E123, Townhall

    F123, Katumba

];

T2:

Load AutoNumber(O1) as C1, AutoNumber(O4) AS C4    /// Changed here

Resident OTD2;

//Drop Table TD2;

OTD3:

LOAD * INLINE [

    O5, O4

    Northern place, NorthSydney

    City CBD, Townhall

    Rural Region, Katumba

];

T3:

Load AutoNumber(O4) as C4, O5 AS C5

Resident OTD3;

//Drop Table TD3;

Hope this help syou.

Regadrs,

Jagan.

[shyamcharan]

Thanks Guys. That helped.

However, I started reading more about Autonumber() and getting worried on using this.

In my actual design I have more than 10 tables and I used Autonumber on all of them. Will that be a problem?

I will use the Autonumber in the tables on both sides of the joins.

However, I am trying to get clear picture on how Autonumber() works on multiple tables in the same load script.

I appreciate your support. Thanks heaps.

Regards,

Shyam.

[jagan]

Hi,

I think there is no necessity to use it mandatorily, even without this the join works.  If you are using then you have to use it carefully for all the key columns you are using otherwise the join will not happen properly likely the one you faced earlier.

Regards,

jagan.