Grouping data from a table using fields from a second table

[#1]

Hello,

I need to create a report for earnings and expenses based on accounting records.

So I have a table that contains all detailed earnings and expenses, where the account numbers are 6 numbers format (such as 700110, 606300 and so on).

I have no problem creating a new table grouping by the account number.

But here comes the tricky part : the report I need to create use another aggregation criteria.
For this, I have a second table that lists the aggregation criteria :

700
72100
730
60
6061
60630
.....
68100
68174
69
75
76
77
78
79

For example, the first account is 700. This means that I need to sum all amounts from accounts that begin with 700, which would be for example 700110, 700190 and so on.

Beware: if you look at the 4th to 6th line, you have accounts number 60, 6061 and 60630. This means that the account 60 must hold all accounts beginning with 60, including accounts 6061 and 60630 !

Of course, the resulting table should display the accounts in the same order as the second table...

Thanks

Vincent

[#2 · Accepted solution]

OK I tried this :

FOR each Account in '700','730','60','6061','60630',61,612,613,615,616,618,62,621,622,623,624,625,626,627,628,63,631,63380,635,636,637,638,64,641,645,647,65,66,67,671,672,675,678,68,68100,68174,69,75,76,77,78,79,79

LOAD
AccountBalance,
AccountLabel,
Month,
left(AccoutBalance,,len('$(Account)')) as NewAccount
RESIDENT AccountBalances
WHERE left(AccountBalance,len('$(Account)'))='$(Account)';

NEXT Account;

It works, although it is not super elegant to say the least.

It would be great if I could load the new account numbers values in the FOR EACH STATEMENT from a file, but I did not succeed...yet.

If you have a better way to do that (I guess there is), please express yourself !

Vincent