Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to group fields in dimension column

Hi All,

Can somebody help me in this :

My input table is :

NameFruitQty
AApple2
BApple3
AOrange2
AGrapes3
BOrange2
COrange3
AGrapes2

& my desired output table is :

FruitMbr CntSum Of Qty
Apple00
Orange13
Apple+Orange25
Apple+Orange+grapes19

I m using PE , so pls help by giving sme code ideas ....

Regards,

Bhushan N

1 Solution

Accepted Solutions
maxgro
MVP
MVP

just an idea but Apple is missing

dimension

=aggr(concat(distinct Fruit, '+'), Name)

expression

=sum(Qty)

=count(DISTINCT Fruit)

1.png

View solution in original post

4 Replies
maxgro
MVP
MVP

just an idea but Apple is missing

dimension

=aggr(concat(distinct Fruit, '+'), Name)

expression

=sum(Qty)

=count(DISTINCT Fruit)

1.png

Not applicable
Author

Bang on ..............

Thanks alot .... I m OK with missing "Apple" from above report ....

MarcoWedel

Hi Bhushan,


another method at script level instead:


tabInput:

LOAD Name,

     Fruit,

     Qty

FROM [http://community.qlik.com/thread/113419]

(html, codepage is 1252, embedded labels, table is @1);

tabTemp:

LOAD

  Name,

  Concat(DISTINCT Fruit, '+') as Fruits,

  Sum(Qty) as [Sum Of Qty]

Resident tabInput

Group By Name;

tabOutput:

LOAD

  Fruits,

  Sum([Sum Of Qty]) as [Sum Of Qty],

  Count(DISTINCT Name) as [Mbr Cnt]

Resident tabTemp

Group By Fruits;

DROP Table tabTemp;

QlikCommunity_Thread_113419_Pic1.JPG.jpg

QlikCommunity_Thread_113419_Pic2.JPG.jpg

I thought you might have had the number of "names" in your mind when defining the "Mbr Cnt" column.

regards

Marco

Not applicable
Author

Thanks alot .....

Actually your input triggers lot many action points here and credit goes to you for that .

Recently we checked that R can be integrated with Qlikview , so now we are in process of creating single view in QV which can be filtered on realtime & clusters can be made out of this thru R .

Currently i am stuck in reading input files from a input folder & shifting selected files to Transformation folder where folder where QVW gets loaded .  Any advice will be of great help.

Thanks