topic Re: Set Analysis instead of Pick in QlikView

Set Analysis instead of Pick

Yevhenii_Senko — Sat, 14 Sep 2019 20:50:31 GMT

Hi,

Please see data example below.

ProductGroup,	StockColor,	StockSize,	StockQuantity,
ProductGroup1,	Green,	40,	10,
ProductGroup1,	Green,	41,	15,
ProductGroup1,	Red,	40,	5,
ProductGroup1,	Red,	41,	5,
ProductGroup2,	Yellow,	Small,	5,
ProductGroup2,	White,	Large,	15,

ProductGroup,	SalesProductName,	SalesColor,	SalesSize,	SalesQuantity,
ProductGroup1,	Shoes,	Green,	40,	3,
ProductGroup1,	Shoes,	Green,	41,	5,
ProductGroup1,	Shoes,	Red,	40,	2,
ProductGroup1,	Shoes,	Red,	41,	2,
ProductGroup2,	Toy,	,	,	11,
ProductGroup2,	Toy,	,	,	8,

In the current data model these two Fact tables are joined by ProductGroup.

However in a chart it should be presented by all dimensions (Group, ProductName, Color, Size) with correct values per row for StockQuantity and SalesQuantity measures.

Some products doesn't have Color and Size therefore key cannot be combined using these columns.

Existing data model is complex. I am thinking to move Products where available Size and Color in a different Table.

I am curious whether is possible to create an expression with only Set Analysis (e.g. {<StockColor=P(SalesColor), StockSize=P(SalesSize)>} or something like {<StockColor={"=Only(SalesColor)"}, StockSize={"=Only(SalesSize)"}>} )

It doesn't work properly.

In addition I have created the expression with Pick function where result as expected.

Pick(Match(StockColor,'Green','Red')
// Green
,Pick(Match(StockSize,'40','41')
,Sum({<StockColor={'Green'}, SalesSize={'40'}>}StockQuantity) // 40
,Sum({<StockColor={'Green'}, SalesSize={'41'}>}StockQuantity) // 41
)
// Red
,Pick(Match(StockSize,'40','41')
,Sum({<StockColor={'Red'}, SalesSize={'40'}>}StockQuantity) // 40
,Sum({<StockColor={'Red'}, SalesSize={'41'}>}StockQuantity) // 41
)
)

My question is how to replace Pick function with Set Analysis hence leave only one formula?

Any help will be much appreciated.

Re: Set Analysis instead of Pick

Yevhenii_Senko — Mon, 16 Sep 2019 19:33:18 GMT

Hi @sunny_talwar ,

Could you please take a look at my post?

Re: Set Analysis instead of Pick

sunny_talwar — Tue, 17 Sep 2019 11:13:15 GMT

I don't think this can be done using set analysis... one option which might work is to use an if statement.

Re: Set Analysis instead of Pick

Yevhenii_Senko — Tue, 17 Sep 2019 11:21:58 GMT

Thanks Sunny, I thought the same regarding to Set Analysis.

From different discussions I found pick should work faster than if. Could you please confirm this assumption?

Thanks

Re: Set Analysis instead of Pick

sunny_talwar — Tue, 17 Sep 2019 11:24:09 GMT

I think that assumption is more often true, but I would suggest you to test it out for yourself.... Try this

Sum(If(StockColor = SaleColor and SaleSize = StockSize, StockQuantity))

Re: Set Analysis instead of Pick

Yevhenii_Senko — Tue, 17 Sep 2019 11:35:28 GMT

Thanks much! I will try it!

Re: Set Analysis instead of Pick

Yevhenii_Senko — Tue, 17 Sep 2019 14:19:03 GMT

I have tested both approaches where identified longer calc time with using pick function.

This testing is acceptable for the existed data model and perhaps might show another results in a different app.

BTW is there only one option to check object resources in the document properties? I mean without using the Document Analyzer. Is it possible to create a chart where to present resources consuming per sheet/objects?

Re: Set Analysis instead of Pick

sunny_talwar — Tue, 17 Sep 2019 14:24:51 GMT

Document Analyzer and Document properties are the only two places I am aware of where you can check this.