topic Re: Aggregate certain entries in QlikView

Aggregate certain entries

MikeJohn — Sat, 16 Nov 2024 18:45:09 GMT

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?

Re: Aggregate certain entries

rubenmarin — Wed, 22 Apr 2020 13:26:03 GMT

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?

Re: Aggregate certain entries

MikeJohn — Wed, 22 Apr 2020 13:31:15 GMT

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

Re: Aggregate certain entries

rubenmarin — Wed, 22 Apr 2020 13:47:47 GMT

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') ;

Re: Aggregate certain entries

Vegar — Wed, 22 Apr 2020 13:53:33 GMT

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

Re: Aggregate certain entries

MikeJohn — Wed, 22 Apr 2020 14:05:38 GMT

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

Re: Aggregate certain entries

MikeJohn — Wed, 22 Apr 2020 14:27:26 GMT

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?

Re: Aggregate certain entries

rubenmarin — Wed, 22 Apr 2020 15:42:20 GMT

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.

Re: Aggregate certain entries

MikeJohn — Wed, 22 Apr 2020 17:42:47 GMT

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

Re: Aggregate certain entries

Vegar — Wed, 22 Apr 2020 19:33:04 GMT

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

Re: Aggregate certain entries

rubenmarin — Thu, 23 Apr 2020 06:46:23 GMT

And wich values should have FieldA, FieldB and ID? there should be 2 rows with number 57?

It it's only one row you can do using TypeSelector as dimension (and you can change field names in script to the ones you want) and using Sum(Number) as expression

Write in a post what's the expected result for the full table.

Re: Aggregate certain entries

Brett_Bleess — Tue, 19 May 2020 18:58:21 GMT

If Ruben or Vegar's latest posts helped you get things working, we would appreciate it if you would return to your thread and properly close it out by using the Accept as Solution button on the post(s) that got you the information you needed to get a working solution. I am assuming one of the last two posts on the thread helped, but I do not know which one, so I could not mark this for you...

Regards,
Brett

Re: Aggregate certain entries

Saravanan_Desingh — Wed, 20 May 2020 00:57:54 GMT

One solution is, create a new Field.

tab1: LOAD IterNo() As ID ,Pick(Ceil(6*Rand()),'A','B','C','D','E','F') As FieldA ,Pick(Ceil(6*Rand()),'F','E','D','C','B','A') As FieldB ,'Type'&Ceil(Rand()*25) As Type ,Ceil(100*Rand()) As Number AutoGenerate 1 While IterNo()<=25; TypeExt: LOAD * INLINE [ TypeExt, Type TypeX, Type17 TypeX, Type23 TypeX, Type1 TypeY, Type18 TypeY, Type3 TypeY, Type20 ]; Concatenate(TypeExt) LOAD Distinct Type, Type As TypeExt Resident tab1; Left Join(tab1) LOAD * Resident TypeExt; DROP Table TypeExt;