Changing field values

[#1]

Hello,

I have two tables that are related by the field id:

Table 1

id	offer_type
1	A
2	B
3	C

Table 2

id	Click_type
1	2
1	4
2	3
2	4
3	3
1	2
3	3

I need to change the values from the field click_type based on the offer type. For example:

Offer_type A has Click_type 2 and 4. And I want to replace 2 for 1 and 4 for 2.

Offer_type C has Click_type 3. And I want to replace 3 for a 5.

New Table 2:

id	Click_Type
1	1
1	2
2	3
2	4
3	5
1	1
3	5

How can I use mapping here? I need this changes to be made in the loading script.

Thank you,

Juan.

[#2]

simply

Table1:

LOAD * Inline [

id,    offer_type

1,    A

2,    B

3,    C

];

Table2:

LOAD *,RecNo() as    Click_type_new 

Inline [

id,    Click_type

1,    2

1,    4

2,    3

2,    4

3,    3

];

then output like this

id	Click_type	Click_type_new
		-
1	2	1
1	4	2
2	3	3
2	4	4
3	3	5

[#3]

Hi,

I agree with vishwaranjan's point.

Regards

Garry

[#4]

Hello guys,

Your solution was right for the previous example, but I have unlimited records in table 2. That solution returns the row number, right? I just edited the example with new records for table 2 so you can see that the solution is not a secuencial number. Any ideas?

Thank you,

Juan.

[#5]

Hello,

I can't open your file because I have the free edition.  Could you post your solution in text please?

Thank you,

Juan.