Need Help

[paulwalker]

Hi Community,

I have one requirement..

I have 2 tables

Table1:

ID, Value

100, 2500

200, 3000

300, 3500

Table2:

ID,  Value

100, 1000

200,  5000

300,  2500,

400,  4500

500,  2000

Here, ID values 100, 200, 300 are same but values are different..

I want to replace the Values in Table2.

My Output should be

Output:

ID, Value

100, 2500

200,  3000

300,  3500,

400,  4500

500,  2000

Thanks in Advance...

[Ralf-Narfeldt]

Table1:

Load * Inline [

ID, Value

100, 2500

200, 3000

300, 3500];

Load * Inline [

ID,  Value

100, 1000

200,  5000

300,  2500,

400,  4500

500,  2000] Where Not Exists(ID) ;

[Anonymous]

try this:

Table1:

load ID, Value

from ...

Table2:

noconcatenate load ID, Value

from  ...

where not exists (ID)

left join(Table2)

load *

resident Table1

drop Table1;

now your table 2 holds your desired values, maybe you want to sort your table

[sorrakis01]

Table1:

LOAD * INLINE [

ID, Value

100, 2500

200, 3000

300, 3500

];

JOIN (if you like is not necesary)

Table2:

LOAD * INLINE [

ID,  Value

100, 1000

200,  5000

300,  2500,

400,  4500

500,  2000

]

where not exists(ID);

[pokassov]

Hello!

MapTab:

Mapping

load

ID, Value

resident Table1;

Table2:

Load

ID,

ApplyMap('MapTab',ID,Value)          As Value

from ...;

[maxgro]

another one, using applymap with 3 parameters, could be

Table1:

mapping load * inline [

ID, Value

100, 2500

200, 3000

300, 3500

];

Table2:

load

  ID,

  ApplyMap('Table1', ID, Value) as Value

inline [

ID,  Value

100, 1000

200,  5000

300,  2500,

400,  4500

500,  2000

];

[sasiparupudi1]

Table1:

mapping LOAD * Inline

[

ID, Value

100, 2500

200, 3000

300, 3500

];

NoConcatenate

Table2:

load

ID,ApplyMap('Table1',ID,Value) as Value

inline

[

ID,  Value

100, 1000

200,  5000

300,  2500

400,  4500

500,  2000

];

[paulwalker]

If i have duplicate ID's,

Is this work ApplyMap perfectly???

[maxgro]

do you mean like this (duplicated in mapping table)? 100 is duplicated

Table1:

load * inline [

ID, Value

100, 2500

200, 3000

300, 3500

100, 999999999

];

mapping load * Resident Table1 order by Value desc;

DROP Table Table1;

Table2:

load

  ID,

  ApplyMap('Table1', ID, Value) as Value

inline [

ID,  Value

100, 1000

200,  5000

300,  2500,

400,  4500

500,  2000

];

I think the applymap uses the first one, in that case 9999..... because of the sort in resident load

and this without sort

[sebastianlettner]

Hi,

here is an example without Mapping

TableA:

LOAD * INLINE

[

    ID, Value

    100, 2500

    200, 3000

    300, 3500

];

TableB:

NoConcatenate

LOAD * INLINE

[

    ID, Value

    100, 1000

    200, 5000

    300, 3500

    400, 4500

    500, 2000

];

Outer Join(TableB)

LOAD

    ID,

    Value as ValueA

Resident TableA;

Output:

NoConcatenate

LOAD

    ID,

    if(not IsNull(ValueA), ValueA, Value) as Value

Resident TableB;

drop tables TableA, TableB;