Combine two tables, but ID should be unique

[kalyandg]

Hi All,

I have a requirement that

     1. Hardcoded records (5) should be concatenated to the below tables. it has a ID column which start with 1 - 5 as the rows, along with Desc column.

     2. Second, i load the table from DB, which has ID and Desc, ID is autonumber of Desc column,

     3. it will autoconcatenate, and results will be like ID has duplicate values from Hardcoded inline and Db. But i dont want this result.

     4. I require the result like, ID first 5 rows from Hardcoded inline and next onwards from another table, which should be sequential, ID column - Values should be unique.

Please help me to complete this requirement.

Thanks in Advance

Best Regards,

Kalyan

[muthukumar77 · Accepted solution]

Hi,

May be like this,

T1:

LOAD * INLINE [ //Table 1

    ID, Desc

    1, A

    2, B

    3, C

    4, D

    5, E

];

MaxNo:

LOAD max(ID) as MaxID Resident T1;

LET vmaxNo= Num(Peek('MaxID', 0, 'MaxNo'));

DROP Tables MaxNo;

Concatenate(T1)

LOAD ID + $(vmaxNo) as ID,Desc; //Table 2

LOAD * INLINE [

    ID, Desc

    1, F

    2, G

    3, H

    4, I

    5, J

];

[jonathandienst]

You can use RowNo() to sequentially number the rows.

Data:

LOAD * Inline

[

     ID, Desc

...

];

Concatenate(Data)

LOAD RowNo() as ID,

     Desc,

  ...

;

SQL SELECT

     Desc,

     ...    

FROM databasetable

;

RowNo() will automatically start at 6 for the DB rows.