Conditional Queries with Data Islands

[#1]

Hi,

I have 2 fact tables, Transactions and Visits, which are linked together by a field called Site ID. I'm using data islands on date and country so that I can show traffic and sales not only on the Site ID dimension, but also on the country and date dimensions:

However, the conditions I'm using within qlikview  seem to be slowing performance.

For instance, to show visits and transactions together by country and date, I do the following for visits:

sum(if([Visit Date]=[Island Date] AND [Island Country]=[Visit IP Country ISO],[Visit Counter]))

To show visits and transactions together by country and date, I do the following for transactions:

sum(if([Calendar Date]=[Island Date] AND [Island Country]=[IP Country ISO],_f_OK_SIGNUP))

These queries work, but are very slow, to the point I don't think I'll be able to use them for client facing reports. Does anyone have experience with data islands and the most efficient way to use them in queries? Perhpas mine can be optimized to improve performance.

Any insight would be appreciated.

Best,

Matt

[#2]

Thanks Oleg,

I will continue to work on the syntax. I'm also starting to use field event triggers to link dimensions in different tables with different names. This seems to be working well and will allow me to drop the data islands. Still in the preliminary stages, will post back my findings if this turns out to be a success.

Best,

Matt

[#3]

Hi,

I appreciate your help, but for now am going to work on tweaking my set anlysis and implementing event triggers.

I wouldn't want you to work on another solution, since I think I'll be able to move forward using the above.

Thanks again though for the offer.

Matt

[maksim_senin]

Hi Matt,

Recently I had very similar case and I'd suggest you to consider the following solution:

1. Both transactions and visits can be considered as a kind of transactions each of which has several attributes and some of the attributes are common to both of them (site, country, date) whilst some are unique,

2. Instead of creating separate tables it's possible to rename all common fields in order to provide the same names for them and load (concatenate) both tables into a single one. By the way, you still can provide a manner to separate the rows by creating a flag filed with values, e.g., "Transaction" and "Visit" in order to be able to analyse them separately,

3. After that you'll have one fact table with arbitrary number of dimension table (if necessary), you won't have performance issues and data islands which are not recommended for use at all.

You can face some obstacles if you do it for the first time but once do it I believe you'll feel performance improvment.

Best regards,

Maxim

[agigliotti]

i noted using set analysis in this scenario it doesn't works, or generally speaking when you have a chart dimension field in a island table and the measure field in other island table.

i think in this case you MUST use if statement in the expression.

is it true ?

please let me know.

best regards

Andrea