Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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');
......