Load new file and replace the line from old file

[#1]

Hello,

I have some issues updating the new files from a table with data coming from CSVs. But I'll give an example with INLINE Loading.

For example, I have 2 files which are loaded in order Temp1 first and after Temp2.

Temp1:

LOAD * INLINE [

    ID, NAME

    1, Claudiu

    2, Paul

    3, George

];

Temp2:

LOAD * INLINE [

    ID, NAME

    1, Claudiu

    2, Robert

];

What I want to achieve is to update the row where ID = 2 with the value from Temp2 (the newest load) but keep the data from Temp1 (ID 3 from Temp1)

My final table should look like:

ID, NAME

1, Claudiu

2, Robert

3, George

Can you give me some ideas on this matter ?

Thank you!

[Anonymous]

Hi Assuming that Temp2 is your new master I would do something as follows. this is pseudo code

Table:

Load

     *

From Temp2;

Concat(Table)

Load

*

From Temp1

Where Not Exists(ID);

This will load all rows from Temp2 reuslting in rows with IDs 1 & 2.

The second load will only load one row with ID 3 from Temp1

[#3 · Accepted solution]

I've found a solution. If anyone has this issue, this is how I did it:

Temp1:

LOAD * INLINE [

    ID, NAME

    1, Claudiu

    2, Paul

    3, George

];

Concatenate(Temp1)

LOAD * INLINE [

    ID, NAME

    1, Claudiu

    2, Robert

];

Temp3:

LOAD *,

  RowNo() as Index

Resident Temp1;

drop table Temp1;

Final:

  NoConcatenate

  LOAD

  FirstSortedValue(ID,-Index) as IDFinal,

  FirstSortedValue(NAME,-Index) as NAMEFinal

  Resident Temp3

  Group by ID;

I used 'FirstSortedValue' function.

[Anonymous]

‌did you attempt my response. Using where not exists is much less code. This will process quicker and less error prone