Add Data to existing table

[siegbert]

Hi,

i have a masterdata table with "account number" and a field "Category".

Now i want to add a higher category level. My approach:

NoConcatenate
Acount_Master:
Load
account_number,
decription,

category_no,

category_text,
From....;

Concatenate (Acount_Master)
Load
account_number,

decription,
'Category XY' as category_text,
'9' as category_no
resident Acount_Master
where match(category_no,'10','11')
;

I need to do that 20 times or more. The problem is obviously that i multiply the account number which is the key to the transaction data. The result is a very unperformant datamodel.

What is the best way to add these categories without double the account_numbers?

BR

[vivek_gajjar01]

If I've understood your questions correctly, a possible suggestion is that you could do a join rather than concatenating the tables (so effectively you're creating columns of the different category levels rather than creating rows).

e.g:

Acount_Master:
Load
account_number,
decription,

From Accounts;

LEFT JOIN (Acount_Master)

LOAD account_number

 , category  as CategoryL1

, category_no  as as CategoryNoL1

FROM ...

where match(category_no,'1');

LEFT JOIN (Acount_Master)

LOAD account_number

 , category  as CategoryL2

, category_no as CategoryNoL2

FROM ...

where match(category_no,'2');

......