1 Reply Latest reply: Dec 19, 2017 8:07 AM by Nico Kirsch RSS

    How do I group 21 fields into one "field"?

      Hello there everybody!


      So, I have a tiny problem with my script.


      This one:


      LOAD [CNAE 2.0 Classe],

           if([CNAE 2.0 Classe]<'04000','Agricultura') as "CNAE",

            if([CNAE 2.0 Classe]>='05000' and [CNAE 2.0 Classe]<='09000','Industrias Ext') as "CNAE",

            if([CNAE 2.0 Classe]>='10000' and [CNAE 2.0 Classe]<'34000','Industrias Transf') as "Industrias de Transformação",

            if([CNAE 2.0 Classe]>='35000' and [CNAE 2.0 Classe]<'36000','Eletric,Gas') as "Eletricidade e Gás",

            if([CNAE 2.0 Classe]>='36000' and [CNAE 2.0 Classe]<'40000','Agua,Esgoto') as "Água, Esgoto, e afins",

            if([CNAE 2.0 Classe]>='41000' and [CNAE 2.0 Classe]<'44000','Construcao') as "Construção",

            if([CNAE 2.0 Classe]>='45000' and [CNAE 2.0 Classe]<'48000','Comercio/RecVeiculos') as "Comércio/Recuperação de veículos automotores e motocicletas",

            if([CNAE 2.0 Classe]>='49000' and [CNAE 2.0 Classe]<'54000','Transp/Arm/Correio') as "Transporte, Armazenagem e Correio",

            if([CNAE 2.0 Classe]>='55000' and [CNAE 2.0 Classe]<'57000','Alojamentos/Alimentacao') as "Alojamento e Alimentação",

            if([CNAE 2.0 Classe]>='58000' and [CNAE 2.0 Classe]<'64000','Inf/Com') as "Informação e Comunicação",

            if([CNAE 2.0 Classe]>='64000' and [CNAE 2.0 Classe]<'67000','AtvFinan/Seg') as "Atv. Financeiras, de Seguros e Serv. Relacionados",

            if([CNAE 2.0 Classe]>='68000' and [CNAE 2.0 Classe]<'69000','AtvImobiliarias') as "Atv. Imobiliárias",

            if([CNAE 2.0 Classe]>='69000' and [CNAE 2.0 Classe]<'76000','Atv.Profs.Cient.Tec') as "Atv. Profissionais, Científicas e Técnicas",

            if([CNAE 2.0 Classe]>='77000' and [CNAE 2.0 Classe]<'83000','AtvAdm/ServComp') as "Atv. Administrativas e Serv. Complementares",

            if([CNAE 2.0 Classe]>='84000' and [CNAE 2.0 Classe]<'85000','AdmPub/Defesa/SegSocial') as "Adm. Pública, Defesa e Seguridade Social",

            if([CNAE 2.0 Classe]>='85000' and [CNAE 2.0 Classe]<'86000','Educação') as "Educação",

            if([CNAE 2.0 Classe]>='86000' and [CNAE 2.0 Classe]<'89000','SaudeHuman/ServSociais') as "Saúde Humana e Serviços Sociais",

            if([CNAE 2.0 Classe]>='90000' and [CNAE 2.0 Classe]<'94000','Art/Cult/Espor/Recre') as "Artes, Cultura, Esporte e Recreação",

            if([CNAE 2.0 Classe]>='94000' and [CNAE 2.0 Classe]<'97000','OutrasAtv/Serv') as "Outras Atividades e Serviços",

            if([CNAE 2.0 Classe]>='97000' and [CNAE 2.0 Classe]<'98000','ServDomest') as "Serviços Domésticos",

            if([CNAE 2.0 Classe]>='99000' and [CNAE 2.0 Classe]<'98000','OrgInternacionais') as "Org. Internacionais",

           [CNAE 95 Classe],


      As you can see, I created some "ifs" to separate one column into different fields. On the first two lines, there's a 'as "CNAE"' cuz I need to show all these fields in one "big" field, containing them all.


      I can't use the "as "CNAE"" at the end, cuz the QV will return a error, saying that the field names have to be unique