Set Analysis. Sum with help other field.

[#1]

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.

[Anonymous]

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

[ramoncova06]

try with a possible selections in your set analysis

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

[pokassov · Accepted solution]

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

[jonathandienst]

Hi

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

[#6]

Thanks!

Using ApplyMap and Mapping load helped to solve this problem.