Same primary key in a table and different information inrecords

[Alemovaz]

I have records with different contents and same primary key in a table. What happens is the following: the records bring the name of companies with one, two, or more characters of difference in their names due to entries in different reference databases. However, they bring the same registration code, which in Brazil is called CNPJ, and is the primary key of the table. However, I wanted to turn these records with two, three or four different names into a single record with a simplified name (eg the first name of the table with that key, or smaller). The objective is to make a sum of all sales values ​​for the same company code (primary key, that is, CNPJ) but present this simplified name as measure and title.

Thanks in advance.

[sidhiq91]

@Alemovaz  Could you please post some sample data along with the expected output.

[Vegar]

I am trying to understand the  issue. A way of achieving this could be to use map table and applymap. 

NameMap:

Mapping load

CNPJ, Name

From CNPJNameSource;

Load

field1, 

fields,

CNPJ,

applymap ('NameMap' ,CNPJ) as Name

From Source;

[Alemovaz]

Hi Vegar!

Thank you very much!

I will try this.

[Alemovaz]

Hi SidHiq91!

The table is:

CNPJ (Key), Name, Address, Value1,...

123456, Empresa 123, Avenue X num 67, 5000

123456, Empresa 123 X, Avenue X num 67, 8000

123456, Empresa 123 X1, Avenue X num 67, 7000

567890, Empresa Y, Avenue Y num 85, 3000

567890, Empresa Y A, Avenue Y num 85, 4000

Whoever made each registration of companies X and Y put more or less information about the name. But they are the same companies respectively.

[Alemovaz]

I want to use only one name for all entries, say: Company X and Company Y in ALL records for each company.

[Vegar]

You could try something like this

MapName:
MAPPING LOAD
CNPJ, Name 
FROM Source;

Data:
LOAD
CNPJ, 
applymap('MapName', CNPJ) as Name, 
Address, 
Value1 
FROM Source
];

OR you can try to make a dimension table for CNPJ Like this.

DIM_CNPJ:
LOAD
CNPJ, 
FirstValue(Name) as  Name
FROM Source
Group by CNPJ;

Data:
LOAD
CNPJ, 
Address, 
Value1 
FROM Source
];

[Alemovaz]

Hi Vegar!

I Will try this today.

My expected result is (Table):

123456, Empresa 123, Avenue X num 67, 5000

123456, Empresa 123 , Avenue X num 67, 8000

123456, Empresa 123, Avenue X num 67, 7000

Without X and X1 in the names.

Thank you very much!