Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a problem which I think can be solved with a mapping load, but I just havent found the right function yet.
What I am trying to do is the following:
I am developing an app to analyse General Ledger Data. Lets assume I have to following entries:
Document No Line No GL Account Computed Field
100001 001 34000 3400042000
100001 002 42000 3400042000
100002 001 34000
100002 002 50000
100003 001 20000
100003 002 46000
100004 001 34000
100004 002 30000
100005 001 10000
100005 002 40000
I want that on selecting a specific GL account (e.g. 34000) all of the corresponding document numbers where this account is referenced are displayed (not only the lines where the account is referenced) are displayed (green document numbers).
How I would solve it is by doing a mapping load that creates a field for each document number where all of the GL account values for that document number are included (e.g.: for Document Number 100001 the Field values for each line would be 3400042000, see also values in red)
With this field I could then check if a GL account occurs in a specific Document No.
I hope I was able to explain my problem and look forward to your ideas!
hi,
please see the solution below,
map:
mapping
load
[GL Account],
[GL Account]&[Document No]
from
GL;
gl_account:
load *,
Applymap('map', [GL Account]) As computedfield
from GL;
Do like this:
Table:
LOAD [Document No],
[Line No],
[GL Account]
FROM Source;
Join (Table)
LOAD [Document No],
Concat([GL Account]) as [Computed Field]
Resident Table
Group By [Document No];
Hi,
thank you for the answers, unfortunately a join is not feasible in this case due to the large amount of data to be computed. With the hardware we have available it is not possible to do a join across this amount of values. Are there any other solutions?
For mapping load solution:
Table:
LOAD * Inline [
Document No, Line No, GL Account
100001, 001, 34000
100001, 002, 42000
100002, 001, 34000
100002, 002, 50000
100003, 001, 20000
100003, 002, 46000
100004, 001, 34000
100004, 002, 30000
100005, 001, 10000
100005, 002, 40000
];
Mapping:
Mapping
LOAD [Document No],
Concat([GL Account]) as [Computed Field]
Resident Table
Group By [Document No];
FinalTable:
LOAD *,
ApplyMap('Mapping', [Document No]) as [Computed Field]
Resident Table;
DROP Table Table;
Thanks alot! This solved it
Did you look at the Mapping Solution above?
Awesome