topic Re: Set Analysis. Sum with help other field. in QlikView

Set Analysis. Sum with help other field.

Sat, 04 Apr 2015 16:34:44 GMT

Hello.

Help to solve my problem. There are 3 tables:

1) Facts

Items	Locations	Qty
Item1	Location1	1
Item1	Location2	1
Item1	Location3	1
Item1	Location4	1
Item2	Location2	1
Item2	Location3	1
Item2	Location4	1
Item3	Location1	1
Item3	Location2	1
Item3	Location3	1

2) LinkItemsAndDim

Items	Dimensions
Item1	Dim1
Item2	Dim1
Item3	Dim2

3) DimensionsSetup

Dimensions	LocationsSetup
Dim1	Location1
Dim1	Location2
Dim2	Location3

Need to sum column Qty only those records where the field Locations match with field LocationsSetup.

DimensionsGroup	QtySum
Dim1	3
Dim2	1

Without Set Analysis formula is as follows:

sum (if (Locations = LocationsSetup, Qty))

But it is uneffective for large data (> 10 million rows).

Help, please, write a formula using the Set Analysis.

Re: Set Analysis. Sum with help other field.

Anonymous — Mon, 06 Apr 2015 15:25:21 GMT

You can load a mapping table

with columns Location,Flag /// use location setup data to load this field

Location1,'1'

.....

now you can use apply map and add flag column to your Fact table. so now your main table have 1 for loactions which are present in DimensionsSetup table

=sum({<Flag='1'>}Qty)

Thanks

BKC

Re: Set Analysis. Sum with help other field.

ramoncova06 — Mon, 06 Apr 2015 15:38:29 GMT

try with a possible selections in your set analysis

sum({<Locations =P(LocationsSetup)>} Qty)

Re: Set Analysis. Sum with help other field.

pokassov — Mon, 06 Apr 2015 15:47:43 GMT

Hello!

I don't think that set analysis can help in this case. I mean 10 million rows...

Can you change your script?

DimensionsSetup:

LOAD Dimensions,

LocationsSetup

FROM

(biff, embedded labels, table is DimensionsSetup$);

LinkItemsAndDim:

LOAD Items,

Dimensions

FROM

(biff, embedded labels, table is LinkItemsAndDim$);

t1:

NoConcatenate LOAD

Resident

DimensionsSetup;

Left Join (t1)

LOAD

Resident

LinkItemsAndDim;

MapTable:

Mapping LOAD

Items,

LocationsSetup

Resident

t1;

DROP Table

t1;

Fact:

LOAD Items,

Locations,

Qty,

if(ApplyMap('MapTable',Items,-1)=Locations,1,0) as _Flag

FROM

(biff, embedded labels, table is Facts$);

Re: Set Analysis. Sum with help other field.

jonathandienst — Mon, 06 Apr 2015 16:18:05 GMT

Use mapping to bring the dimensions into the table, and set a flag for the matches:

MapDims:

Mapping LOAD Items,

Dimensions

FROM [testRangeExt.xls]

(biff, embedded labels, table is LinkItemsAndDim$);

MapLocs:

Mapping LOAD LocationsSetup,

Dimensions

FROM [testRangeExt.xls]

(biff, embedded labels, table is DimensionsSetup$);

Data:

LOAD *,

If(ItemDim = LocationDim, 1, 0) As DimMatch

;

LOAD Items,

Locations,

ApplyMap('MapDims', Items) As ItemDim,

ApplyMap('MapLocs', Locations) As LocationDim,

Qty

FROM [testRangeExt.xls]

(biff, embedded labels, table is Facts$);

Now use either ItemDim or LocationDim as a table dimension, and use the Matchflag in the expression:

=Sum({<DimMatch = {1}>} Qty)

This should perform well on a large data set.

HTH

Jonathan

Re: Set Analysis. Sum with help other field.

Tue, 07 Apr 2015 05:21:16 GMT

Thanks!

Using ApplyMap and Mapping load helped to solve this problem.