match function of two fileds

[Vince_CH]

Dear friends,

I have a question as illustrated like following photo, with two fields like below, two fields has different contents matched to each others. now I want to change all A1,2,3 to A0, and B1,2,3 to B0, and so on, also the behind cust_Branche_Text need to adapt to A0/B0/C0's matching content too. How the expression should be like here? thanks in advance. 

[Channa]

if(cust_Branche_Text like 'A*', 'A0',if(cust_Branche_Text like 'B*','B0'))

[crusader_]

Hi,

Hope I understood your requirements correctly.

Please find attached qvw.

//Andrei

[Vince_CH]

Hello Channa, thanks for reply, but I have much more than A and B in the fields repeatedly.

[Vince_CH]

Hello Andrei, the results looks good to me, it seems in a text box, when I tried to see the scripts behind, it starts standstill, can you copy here what is the expressions behind? thanks a lot.

[Channa]

left('a1',1) &'0'

left (A1,1) will bring A and then concate with 0

[Vince_CH]

Dear Channa, thanks, I want also to bring the content of cust_Branche_Text of X0 to match changed cust_Branche.

[crusader_]

Hi,

Please find below code:

SOURCE_DATA:
LOAD *, RECNO() AS ID;
LOAD * INLINE [
Code, Text
A0, Text0
A1, Text1
A2, Text2
B0, Btext0
C0, Ctext0
D0, Dtext0
D1, Dtext1
];

NewText_Map:
MAPPING LOAD DISTINCT
Code,
Text
RESIDENT SOURCE_DATA
WHERE WILDMATCH(Code,'*0');

Replace_0:
MAPPING LOAD
RECNO() AS from,
0 as to
AUTOGENERATE 9;

RESULTING_TABLE:
LOAD ID,
MAPSUBSTRING('Replace_0',Code) AS NewCode,
APPLYMAP('NewText_Map',MAPSUBSTRING('Replace_0',Code)) AS NewText
RESIDENT SOURCE_DATA;

Hope this helps.

//Andrei

[Vince_CH]

Hello Andrei, thanks a lot, it is close, I think, but I still want the old field name unchanged, since the source table was reloaded again with same fields name included, so I don't want to rename them with newcode or newtext.

Also it seems some $Syn table was created, shall I dropoff the resulting table to reduce this?

Lastly, is what is the meaning of AUTOGENERATE 9 in the scripts?

thanks for the support!

[crusader_]

Hi,

I'll explain script line by line:

SOURCE_DATA:
LOAD *, RECNO() AS ID;        /// This is just for me, to generate unique ID instead of hard-coding (1,2,3,....) - most likely you don't need it.

/////Below I'm loading sample of data
LOAD * INLINE [
Code, Text
A0, Text0
A1, Text1
A2, Text2
B0, Btext0
C0, Ctext0
D0, Dtext0
D1, Dtext1
];

/////This map is required to get a list of <X>0 codes and their descriptions, so I could replace Text later

NewText_Map:
MAPPING LOAD DISTINCT
Code,
Text
RESIDENT SOURCE_DATA
WHERE WILDMATCH(Code,'*0');

//This is a map to replace numbers 1,2,3,4,....9 to 0 if you have more than 9, generate more, e.g. 100 or 1000, what ever number you have, e.g. A125 -> A0

Replace_0:
MAPPING LOAD
RECNO() AS from,
0 as to
AUTOGENERATE 9; //easily could be changed to whatever other suitable number

//I intentionally built 2 separate tables in case you don't want to overwrite values but check the result first... To overwrite you just ned to modify the script as below:

RESULTING_TABLE:
NOCONCATENATE
LOAD ID,
MAPSUBSTRING('Replace_0',Code) AS Code,
APPLYMAP('NewText_Map',MAPSUBSTRING('Replace_0',Code)) AS Text
RESIDENT SOURCE_DATA;
DROP TABLE SOURCE_DATA;

Hope this help.

//Andrei