topic Re: Grouping field values in QlikView

Grouping field values

Tue, 01 Dec 2015 12:30:26 GMT

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!

Re: Grouping field values

karthikeyan1504 — Tue, 01 Dec 2015 13:27:32 GMT

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

Re: Grouping field values

sunny_talwar — Tue, 01 Dec 2015 13:27:34 GMT

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

Re: Grouping field values

Tue, 01 Dec 2015 13:31:02 GMT

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?

Re: Grouping field values

sunny_talwar — Tue, 01 Dec 2015 13:32:54 GMT

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;

Re: Grouping field values

Tue, 01 Dec 2015 13:45:13 GMT

Thanks alot! This solved it

Re: Grouping field values

sunny_talwar — Tue, 01 Dec 2015 16:41:05 GMT

Did you look at the Mapping Solution above?

Re: Grouping field values

sunny_talwar — Tue, 01 Dec 2015 16:58:10 GMT

Awesome