7 Replies Latest reply: Jul 30, 2015 6:37 AM by Michalina Kuczynska RSS

    Help

    Gourav Sikka

      Hi,

         I am New In qlikview.....Please Help Me..

       

       

      Suppose In Table one Field Contain Like Country  US,U.S.A,AMERICA,USA......IWant to all Field Value In One Format Like USA.......So Help Me Which Function I used.....Please Send Me QVW File.....Suppose in Database 2500 records not clear.....then how to resolve this issue In QlikView......please help me

        • Re: Help
          Rudolf Linder

          I would use an inline table or an Excel file with all distinct values of your countries and the wanted outcome

          define it as mappping table and convert your Countryfield according

          short example with inline

          CountryConvert:

          mapping load * Inline [

          Country, CountryShort

          US, USA

          U.S.A, USA

          AMERICA, USA

          USA, USA

          ];

           

          in your fact table:

          FACTTABLE:

          Load

          field1,

          field2,

          applymap('CountryConvert', Country) as NewCountryShort,

          Field3

          ...

          If you have a lot of distinct values it is an awesome one time job

          • Re: Help
            Robert Mika

            You will have to create mapping load table

            One fields with your Country and another one with the correct return:


            Country,ProperCountry

            US,USA

            U.S.A,USA

            AMERICA,USA

            USA,USA


            Here is tutorial how to do it


            Don't join - use Applymap instead


            or you can use this solution


            Missing Manual - Map...using

            • Re: Help
              Sasidhar Parupudi

              Use Mapping load

              See attached

               

              MAP:

              mapping LOAD * Inline

              [

              From,To

              US,USA

              U.S.A,USA

              AMERICA,USA

              IND,INDIA

              CHN,CHINA

              ];

               

              Data:

               

              LOAD ApplyMap('MAP',Country,'NA') as Country,Count

              Inline

              [

              Country, Count

              U.S.A,100

              IND,200

              US,400

              CHN,9000

              ];

              • Re: Help

                Hi,

                use apply map.

                Country:

                Mapping

                Load * inline[

                Country,CountrySame

                US,USA

                United State, USA

                America, USA

                ];

                 

                Maintable:

                LOAD *,

                applymap('Country',field,'NA') as Country

                 

                from table;

                 

                Regards

                Vimlesh

                • Re: Help
                  Marcus Malinow

                  I see you've already asked this question, and received some decent answers.

                   

                  Help

                   

                  Would suggest you show some courtesy to those who have already answered your question by clarifying why their answers don't meet your requirements.

                  • Re: Help
                    Sunny Talwar

                    If you don't want to use Mapping Load, you can fix this with a if statement also:

                     

                    If(Match(Country, 'US', 'U.S.A', 'AMERICA', 'USA'), 'USA', Country) as Country

                    • Re: Help
                      Michalina Kuczynska

                      You can always try to use function match() and define all of the possible versions of names for each country. You will have to nest IF statements in your load script, if you want to re-map more than one country. If no match has been found, word MISSING will be displayed (you can of course change it to anything you like):

                       

                      IF(match(Country, 'US', 'United States', 'America' , 'U.S.A', 'USA', 'AMERICA') , 'USA',

                          IF(match(Country, 'CHN', 'China'), 'CHINA',

                                IF(match(Country, 'IND', 'India'), 'INDIA', 'MISSING'

                                )

                          )

                      ) AS CountrySame

                       

                      But of course - solutions with mapping loads will work for this task too.