Aggregate certain entries

[MikeJohn]

I have a table that has many columns. one of them is called "Type" and another "Number".

E.g.

ID,FieldA,FieldB,Type,Number

1,A,B,Type1,17

2,C,D,Type2,30

3,A,D,Type1,12

4,B,C,Type2,45

There are like 100 different type entries. And after import i can actually choose Type17 and than i get the numbers for Type 17.

I would like to aggregate certain types to a new Entry in Types. (Like if i would multi select them)

For example

TypeX = Type 17+ Type23+Type45

TypeY = Type1 + Type87+Type33

Is it possible to do this during loading and "add" those aggregated types at the beginning of the Type selection field? So it would like like this

TypeX,

Typey,

Type1

Type2

Type3

....

Basically a "shortcut" to a multi selection.

if it is not possible in loading, can I somehow ust create a new multibox with those agregated Types?

[rubenmarin]

Hi, you want to have Type17 to be selectable as a single value and also by TypeX? or Type17 will dissapear as single value and only can be selected as TypeX?

[MikeJohn]

yes i want TypeX and Type17 to be selectable. All the ortiginal entries should still be selectable

[rubenmarin]

To avoid duplicates you can use another table wich creates the different relations between types and aggregations, i made a sample script to show how it can be done:

SET MatchTypeX = 'Type17','Type23';
SET MatchTypeY = 'Type1','Type87';

DataOrig:
LOAD * Inline [
ID,FieldA,FieldB,Type,Number
1,A,B,Type1,17
2,C,D,Type2,30
3,A,D,Type17,12
4,B,C,Type23,45
5,A,C,Type87,87
];

TypeSelector:
LOAD
	Type,
	SubField(TypeSelector, ',') 		as TypeSelector
;
LOAD
	FieldValue('Type', RecNo()) 		as Type,
	If(Match(FieldValue('Type', RecNo()), $(MatchTypeX)), FieldValue('Type', RecNo())&',TypeX'
	  ,If(Match(FieldValue('Type', RecNo()), $(MatchTypeY)), FieldValue('Type', RecNo())&',TypeY'
	  ,FieldValue('Type', RecNo())))	as TypeSelector
AutoGenerate FieldValueCount('Type')
;

[Vegar]

What if you do something like this:

Data:
LOAD * inline [
ID,FieldA,FieldB,%type,Number
1,A,B,Type1,17
2,C,D,Type2,30
3,A,D,Type1,12
4,B,C,Type2,45
];

TypeDim:
LOAD DISTINCT
%type ,
%type as Type
Resident Data;

Concatenate (TypeDim)
LOAD * inline [
%type, Type
Type1, TypeZ
Type2, TypeZ
Type1, TypeW
Type3, TypeW
];

[MikeJohn]

If that would work that would make it nice since i could change the types quite easily.

But i cant make it work. Can you help me there?

This is the loading script:

Data:

// Dummy load so we can use concatenate below

LOAD 0 as dummy AutoGenerate 0;

Concatenate (Data)

LOAD
Type,
Initialen,
Kommentar,
[Number Reg.]

FROM

[foobar\*.xlsx]

(ooxml, embedded labels);

TypeDim:
LOAD DISTINCT
%type ,
%type as Type
Resident Data;

Concatenate Type
LOAD * inline [
%type, Type
Incidents, TypeZ
Meetings, TypeZ
Knowledge, TypeW
Other, TypeW
];

DROP FIELD dummy; // Drop dummy field

[MikeJohn]

Thank you for that code, with it i do actually get to choose TypeX and TypeY.

But they are not added together. They are still shown as "Type17" with the according numbers and "Type23" with the according numbers.

Is it possible to just make this one new "entry" with the name "TypeX" and the numbers are added?

[rubenmarin]

I'm not sure to understand...The field with both values is TypeSelector, have you checked the values in that field?

If this is what you want you only need to change field names.

If it's not please post a sample of a table with the expected result.

[MikeJohn]

On your picture, the right table shows Type17 and Type23 extra.  but i would like to have them show up as TypeX and Number being 57

[Vegar]

I did like this in my previous example:

// Dummy load so we can use concatenate below
Data:
LOAD 0 as dummy AutoGenerate 0;

Concatenate (Data)
LOAD
Type as %type,
Initialen,
Kommentar,
[Number Reg.]
FROM
[foobar\*.xlsx] (ooxml, embedded labels);

TypeDim:
LOAD DISTINCT
%type ,
%type as Type
Resident Data;

Concatenate Type
LOAD * inline [
%type, Type
Incidents, TypeZ
Meetings, TypeZ
Knowledge, TypeW
Other, TypeW
];

DROP FIELD dummy; // Drop dummy field