topic Re: Autonumber() confusion, Using Autonumber in multiple tables in QlikView

Autonumber() confusion, Using Autonumber in multiple tables

shyamcharan — Fri, 12 Feb 2016 00:45:10 GMT

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.

Re: Autonumber() confusion, Using Autonumber in multiple tables

sunny_talwar — Fri, 12 Feb 2016 01:58:36 GMT

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;

Re: Autonumber() confusion, Using Autonumber in multiple tables

jagan — Fri, 12 Feb 2016 02:12:53 GMT

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.

Re: Autonumber() confusion, Using Autonumber in multiple tables

shyamcharan — Fri, 12 Feb 2016 02:21:48 GMT

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.

Re: Autonumber() confusion, Using Autonumber in multiple tables

jagan — Fri, 12 Feb 2016 02:29:03 GMT

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.