topic Re: Same primary key in a table and different information inrecords in Connectivity & Data Prep

Same primary key in a table and different information inrecords

Alemovaz — Fri, 09 Sep 2022 02:07:07 GMT

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.

Re: Same primary key in a table and different information inrecords

sidhiq91 — Fri, 09 Sep 2022 02:24:07 GMT

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

Re: Same primary key in a table and different information inrecords

Vegar — Fri, 09 Sep 2022 05:57:29 GMT

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;

Re: Same primary key in a table and different information inrecords

Alemovaz — Fri, 09 Sep 2022 13:13:32 GMT

Hi Vegar!

Thank you very much!

I will try this.

Re: Same primary key in a table and different information inrecords

Alemovaz — Fri, 09 Sep 2022 13:28:33 GMT

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.

Re: Same primary key in a table and different information inrecords

Alemovaz — Fri, 09 Sep 2022 13:32:24 GMT

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

Re: Same primary key in a table and different information inrecords

Vegar — Fri, 09 Sep 2022 14:12:35 GMT

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 ];

Re: Same primary key in a table and different information inrecords

Alemovaz — Fri, 09 Sep 2022 16:39:09 GMT

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!