topic Re: Set Analysis or Join in QlikView

Set Analysis or Join

Tue, 22 Oct 2013 15:47:48 GMT

Table1 has three fields, a Ticker, Price, and Reference

Table2 has two fields, a Ticker and PickReference

I would like to display in a Straight Table the Ticker, PickReference number, and the corresponding Price

This statement works if only one Ticker is selected: =Sum({<Reference={$(=[PickReference])} >}Price)

Is there a way to change this statement to show the entire table, all tickers, all referenced prices?

Or, would a Join during the load be a better solution?

Attached is my project.

Thanks!

Re: Set Analysis or Join

Gysbert_Wassenaar — Tue, 22 Oct 2013 16:13:54 GMT

If PickReference and Reference contain the same data then rename PickReference to Reference. You could then also join the two tables so you end up with only one. It's not necessary, but an option.

The set expression calculates one set per chart, not a set per row. So it will very likely not do what you expect.

Re: Set Analysis or Join

Anonymous — Tue, 22 Oct 2013 16:40:26 GMT

Hi Chris,

Try the expression.:

FieldValue('Price', FieldIndex('Reference' ,PickReference) )

Please, see attached.

Regards

Re: Set Analysis or Join

Tue, 22 Oct 2013 16:45:52 GMT

If it has to be a sum, you can try something like:

=Sum({<Reference=PickReference >}Price)

Problem is you lose the chart if you clear PickReference selections..

Re: Set Analysis or Join

ronaldocarrijo — Tue, 22 Oct 2013 17:01:03 GMT

Use the formula:

Sum(if(Reference=PickReference,Price))

Re: Set Analysis or Join

Tue, 22 Oct 2013 17:11:53 GMT

Close! But the DORM value is returning the CBST value for that date. Looks like they are all returning the CBST values.

Re: Set Analysis or Join

Tue, 22 Oct 2013 17:14:35 GMT

CBST is returning a value of 126, I don't know where that is coming from.

My formula, close to what you suggested, works when only one Ticker is selected:

=Sum({<Reference={$(=[PickReference])} >}Price)

Re: Set Analysis or Join

Tue, 22 Oct 2013 17:15:47 GMT

Wow! Simplicity!

Thanks!