Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Set Analysis instead of Pick

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

clipboard_image_2.png

 

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

 

Any help will be much appreciated.

 

1 Solution

Accepted Solutions
Highlighted

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

View solution in original post

7 Replies
Highlighted
Contributor III
Contributor III

Hi @sunny_talwar ,

 

Could you please take a look at my post?

Highlighted

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

View solution in original post

Highlighted
Contributor III
Contributor III

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 

Highlighted

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))
Highlighted
Contributor III
Contributor III

Thanks much! I will try it!
Highlighted
Contributor III
Contributor III

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?

Highlighted

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