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. 

[Vince_CH]

Hello Andrei, thanks for the detail explaining. My table source data is a long table quite many fileds included, so I cannot drop it like so. how can keep the table source_date, also without $Syn table? can I drop table RESULTING_TABLE here?

or shall I have to reload the two fields from original table source_date, and make a new table as source for drop later?

[Vince_CH]

Hello Andrei, I have tried following, to reload the two fields of Cust_Branche and Cust_Branche_Text from original table Sales, and create a new source_Date, and scripts like following, finally between resulting table and original sales table is connected by Cust_Branche. I have drop field Cust_Branche_Text from Sales, if not, then a SYN table appears.

But finally the chart looks like below, still wtih certain K5, K8 existed, shoud be K0 instead, also the text behind is missing, can you help to rectify this? thanks in advance. 

Soure_Date:

LOAD *, RECNO() AS ID;
Load
Cust_Branche,
Cust_Branche_Text
Resident Sales;
Drop field Cust_Branche_Text from Sales;

NewText_Map:
MAPPING LOAD DISTINCT
Cust_Branche,
Cust_Branche_Text

RESIDENT Soure_Date
WHERE WILDMATCH(Cust_Branche,'*0');

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

RESULTING_TABLE:
NOCONCATENATE
LOAD ID,
MAPSUBSTRING('Replace_0',Cust_Branche) AS Cust_Branche,
APPLYMAP('NewText_Map',MAPSUBSTRING('Replace_0',Cust_Branche)) AS Cust_Branche_Text
RESIDENT Soure_Date;
Drop Table Soure_Date;

[crusader_ · Accepted solution]

ok, SOURCE_DATA table in my script exists just because I don't have any other tables (Sales) in your case, you don't need it at all..

NewText_Map:
MAPPING LOAD DISTINCT
      Cust_Branche,
      Cust_Branche_Text
RESIDENT Sales
WHERE WILDMATCH(Cust_Branche,'*0');

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

RESULTING_TABLE:
NOCONCATENATE
LOAD <list of your fields of Sales table except "Cust_Branche" and "Cust_Branche_Text">,
      MAPSUBSTRING('Replace_0',Cust_Branche) AS Cust_Branche,
      APPLYMAP('NewText_Map',MAPSUBSTRING('Replace_0',Cust_Branche)) AS Cust_Branche_Text
RESIDENT Sales;

DROP TABLE Sales;

Another thing, could you please check Value of "Cust_Branche" field for unexpected "Cust_Branche_Text" values (e.g. K8, K5 etc) ? If the value the same K8, K5, then for some reason MapSubString() function doesn't replace your number with 0 values... here you need to dig into...

Is it happens randomly or you can notice a pattern?

//Andrei

[crusader_]

Also check ASCII code of your symbols from Cust_Branche field with "5" (which should be 53). To do so, select values one by one and use KPI object with below formula:

=ORD(RIGHT(Cust_Branche,1))

...

And of course check if you have extra spaces from left or right side in Cust_Branche... Use below formula in separate KPI object (number should be 2 (the same as your X0 length):

=LEN(Cust_Branche)

If the length is different add trim() function in above script around Cust_Branche.

Hope this helps. (I can't replicate the issue on my side, so trying to guess what could be wrong with input data)

//Andrei

[Vince_CH]

Hello Andrei, thanks a lot, the source data was from Sap, so it should be in same format. anyway, I will try and double check soon, and keep you updated.

[Vince_CH]

Hello Andrei, it works now, thanks a lot