Adding a column to a concatenated table

[hobanwashburne]

I have two tables that I want to first concatenate, then add a Unique Key (via RowNo()) to the concatenated table. How do I force the concatenation first before adding the Key?

[datanibbler]

Hi Scott,

well, you need to compute the key as an additional column in the final table - so I guess you'll need a RESIDENT LOAD - put the two LOADs (for the concatenation) first and then load the resulting table RESIDENT and add the calculated key.

[ashfaq_haseeb · Accepted solution]

Hi

Temp:

Load

A,

B

From Table1;

concatenate

Load

A,

B

From Table2;

Main

Load *,rownno() as Key;

resident Temp;

drop table Temp;

Regards

ASHFAQ

[maxgro]

// make 2 tables

test1:

load rand()*1000 as f1, rand()*100 as f2

AutoGenerate 1000;

test2:

load rand()*1000 as f1, rand()*100 as f3

AutoGenerate 1000;

// concat 2 tables

t:

NoConcatenate load * Resident test1;

Concatenate (t) load * Resident test2;

// add unikey key

rename table t to tmp;

t:

load rowno() as id, * Resident tmp;

DROP Table tmp, test1, test2; 

[mrybalko]

RowNo() function works correct in concatenation. You do not need to make resident load.

Facts:
LOAD 
    RowNo() as id,
    A
INLINE [
    A
    1
    2
];

Concatenate(Facts)
LOAD 
    RowNo() as id,
    A,
    B
INLINE [
    A, B
    1, 11
    2, 22
];