topic Re: How to group fields in dimension column in QlikView

How to group fields in dimension column

Mon, 07 Apr 2014 15:00:38 GMT

Hi All,

Can somebody help me in this :

My input table is :

Name	Fruit	Qty
A	Apple	2
B	Apple	3
A	Orange	2
A	Grapes	3
B	Orange	2
C	Orange	3
A	Grapes	2

& my desired output table is :

Fruit	Mbr Cnt	Sum Of Qty
Apple	0	0
Orange	1	3
Apple+Orange	2	5
Apple+Orange+grapes	1	9

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

Regards,

Bhushan N

Re: How to group fields in dimension column

maxgro — Mon, 07 Apr 2014 19:43:25 GMT

just an idea but Apple is missing

dimension

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

expression

=sum(Qty)

=count(DISTINCT Fruit)

Re: How to group fields in dimension column

Tue, 08 Apr 2014 12:44:41 GMT

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

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

Re: How to group fields in dimension column

MarcoWedel — Tue, 08 Apr 2014 22:10:28 GMT

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;

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

regards

Marco

Re: How to group fields in dimension column

Sun, 20 Apr 2014 05:56:06 GMT

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