Change to sentence case

[srired]

Hi All

We are facing an issue during data migration.

Existing system has Country, as the UDC description stored in UDC 00/CN (country codes). We have to fetch the Country based on this description and put in JDE. We could not find any standard JDE function which retreive the UDC value based on its first description. So we are doing table IO operation.



The description in the JDE is in sentence case(Some countries have more than one word in sentence case. For instance, United Kingdom), while it is not the same from the source. Certain data is in Upper case, some is in lower etc.... We know we can convert to either lower case or upper case using system functions but not into sentence case.I have to convert it to sentence case and fetch it from UDC, else fetch fails.

Any one can please suggest, if we have any function which takes care of this part and returns the UDC value or a way in JDE to change to sentence case.

Thanks in Advance

Srired

E810/E812

 

[Ramprakash]

Hi,

I think, its better to pick the record and truncate the spaces between the words [United Kingdom into UnitedKingdom]. So that we can compare the value with user input description.

Hope this helps.

 

[srired]

Hi Ram

Thanks for your reply. We are missing somewhere here, I think i did not explain it properly.

I will try to explain it better. I am comparing country value from the external source against UDC description and getting Country code. I have to format the incomming value as per JDE and get the UDC value. As i mentioned the value which comes like 'UNITED KINGDOM' should be made as 'United Kingdom', check against UDC description and get country code as 'UK' from 00/CN.

All suggestions are welcome.


Thanks again...
Srired
E810/E812

 

[JMast]

One possibility would be to use a spreadsheet program like Excel. This will add some steps to the process and can be done many ways, but here is a rough suggestion.

1. Create a list from the old system of the unique country values.
2. Put this list in Excel and use the =PROPER() formula to put the country in sentence format.
3. Extract the UDC and descriptions from JDE and put them into Excel.
4. Use a =VLOOKUP() in Excel to put the JDE UDC code with each country from the old system
5. Find a field in the old system to import the JDE UDC into for the conversion. (Be careful about how JDE stores the UDC with spaces attached).

I hope this makes sense. Let us know if you have questions.

Jer

 

[Alex_Pastuhov]

There are not so many countries in the world to need a generic solution, you can probably achieve this easier by hardcoding the names, as in:

if upprcase(country) = 'UNITED KINGDOM' then
country = 'United Kingdom'

and so forth. You will likely only need to do this 3-4 times and then process all the rest in the final ELSE.

How does this sound?

 

[craig_welton]

Hi,

Perhaps you can create a new UDC table that is a copy of 00/CN. Update all the descriptions to upper case using SQL. Have your program convert the source data to upper case use the new UDC table to get the contry code.

Craig

 

[Adrian_Chimirel]

I would use the B0100009 F0101 Edit Compressed Description bsfn to compress both entities (country descriptions).
Use a while loop (Select) to retrieve ALL 00|CN records, then FetchNext, and compare your compressed values.
When you have a match, retrieve the country code, and get out from the loop.