Grouping field values

[#1]

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!

[karthikeyan1504]

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

[sunny_talwar]

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

[#4]

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?

[sunny_talwar]

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;

[#6]

Thanks alot! This solved it

[sunny_talwar]

Did you look at the Mapping Solution above?

[sunny_talwar]

Awesome