Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
abhonsle
New Contributor III

Standardize values in a "Region" field

Hi All,

I have a Region field in my Eloqua data base with around 302 values. However the way it is stored is

There are a values with upper case and lower case eg: UNITED STATES, United States and unites states. Similarly for VIETNAM and Vietnam  would like to have a standardized format for these values.

How can this be achieved?

9 Replies
vishsaggi
Esteemed Contributor III

Re: Standardize values in a "Region" field

May be try this:

LOAD Upper(Region) AS Region inline [

Region

United States

UNITED STATES

United KINGDOM

Virgin Islands, British

Virgin ISLANDS, U.s.

Virgin ISLANDS, U.s.

UZBEKISTAN

Uzbekistan

];

Then use Region as Listbox, it would display distinct Regions. If your requirement is different please elaborate a little.

abhonsle
New Contributor III

Re: Standardize values in a "Region" field

Hi Vishwarath,

Thank you for your response. the use of inline is  manual entry in Load script. However If a user enters a new region I will have to update this manually which I am not looking to have. Is there a different method where I don't have to update the field manually.

Re: Standardize values in a "Region" field

When you talk about Manually, You can do this? Check as per your need. I am sure Viswanath solutions works if not you would provide output also if you know

LOAD If(SubStringCount(Region, 'United States'), 'UNITED STATES',If(SubStringCount(Region, 'Vietnam'), 'VIETNAM',Region)) AS Region, Capitalize(Region) as Region1 inline [

Region

United States

UNITED STATES

United KINGDOM

Virgin Islands, British

Virgin ISLANDS, U.s.

Virgin ISLANDS, U.s.

UZBEKISTAN

Uzbekistan

VIETNAM

Vietnam

];

Life is so rich, and we need to respect to the life !!!
vishsaggi
Esteemed Contributor III

Re: Standardize values in a "Region" field

I just showed you an example using Inline. In your actual load script just use like below where you have the Region field coming. Like below:

LOAD col1,

           col2,

           ....,

           Upper(Region) AS Region,

          Col4....

FROM yourdatasourcename;

Not applicable

Re: Standardize values in a "Region" field

Hi Ajinkya

    I think he means you should input 'Upper(Region)  as Region '  when you load the source data. Therefore  all the  values of  'Region'  will in Uppercase.

abhonsle
New Contributor III

Re: Standardize values in a "Region" field

Great got it Bob!. Thank you so much. It worked. All I had to do is use UPPER(Region) as Region.

One other question I have is there are some values like below which needs to be renamed. How should I rename it and as well as keep the Capitalization intact

   

IN
INDIA

   

UNITED STATES MINOR OUTLYING ISLAND
UNITED STATES MINOR OUTLYING ISLANDS

Re: Standardize values in a "Region" field

Hi,

If possible maintain a mapper table which have this combination.

Regards,

Prashant

Great dreamer's dreams never fulfilled, they are always transcended.
Not applicable

Re: Standardize values in a "Region" field

Hi Ajinkya


    I think it's good to make a mapping table.

    Mapping table:

    load * inline

    [ Region,Region2

      US,US

      IN,INDIA

UNITED STATES MINOR OUTLYING ISLAND,UNITED STATES MINOR OUTLYING ISLANDS ] ;

 

    Then ' Region2' will be  the dimension you want .


Regards

Bob


   



Re: Standardize values in a "Region" field

Note that if your source system grows and doesn't impose limitations on the allowed Region field values, it's close to impossible to create a solution that handles all possible anomalies now and in the future. There are too many possibilities (a single missing punctuation mark will create a new entry)

You will end up with manual work (either in script code, in an include file, in a specific translation/mapping table in a DB or in an Excel) in any case.

Community Browser