topic Re: Conditional Queries with Data Islands in QlikView

Conditional Queries with Data Islands

Thu, 31 Jan 2013 20:56:39 GMT

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

Re: Conditional Queries with Data Islands

Anonymous — Thu, 31 Jan 2013 21:03:17 GMT

Dear Mattsies,

better one is give link two island to visit table and create a flags in both island,you can take those both flags in setanalysis,your data will be filter based on noth flgs also.

Regards

Re: Conditional Queries with Data Islands

rwunderlich — Thu, 31 Jan 2013 21:26:23 GMT

Try it using Set Analysis instead of if().

-Rob

Re: Conditional Queries with Data Islands

Fri, 01 Feb 2013 02:07:00 GMT

Hi Rob!

I'm trying to do this with set analysis but not sure I have the right syntax:

Neither of the set analyses below work, but as I said I'm not sure my syntax around the island date and country is correct:

sum({<[Calendar Date]=[Island Date] , [Island Country]=[IP Country ISO]>}_f_OK_SIGNUP)

sum({<[Calendar Date]={"Island Date"} , [Island Country]={"IP Country ISO"}>}_f_OK_SIGNUP)

Any glaring mistakes you see?

Thanks again, your help is much appreciated.

Matt

Re: Conditional Queries with Data Islands

Fri, 01 Feb 2013 02:07:50 GMT

Thanks, allow me some time to review this option and I'll get back to you if it works.

Matt

Re: Conditional Queries with Data Islands

rwunderlich — Fri, 01 Feb 2013 03:38:08 GMT

You need to use a $ function. Here's a working example from one of my QVWs.

=sum({<Date={"$(=only([Island Date]))"}>}Sales)

Be sure to make your date text formats line up as this will be a text compare. Meaning, if Date is MM/DD/YYYY and Island Date is M/D/YYYY it won't work for dates like 08/02/2012 <> 8/2/2012.

-Rob

http://robwunderlich.com

Re: Conditional Queries with Data Islands

Anonymous — Fri, 01 Feb 2013 05:06:47 GMT

hi,

it is possible to provide sample data for your requirement.

Re: Conditional Queries with Data Islands

Fri, 01 Feb 2013 16:07:35 GMT

Yes, here's a sample qvw.

I'm working on incorporating Rob's set analysis but am having trouble getting the figures to display correctly.

Thanks again for the help.

Matt

Re: Conditional Queries with Data Islands

Oleg_Troyansky — Fri, 01 Feb 2013 19:43:54 GMT

Mattsies, I agree with Rob's suggestion to use set analysis instead of IFs. Once you work out the syntax, it will work, even though with some limitations.

Fundamentally, however, your application will be much better off if you could resolve the issue through Data Modeling and avoid having data islands. This approach would ultimately solve the problem, once and forever...

cheers,

Oleg

Re: Conditional Queries with Data Islands

Anonymous — Sat, 02 Feb 2013 13:13:10 GMT

hi,

can you please forward test data,for creating ineed some data

Thanks

Regards

Re: Conditional Queries with Data Islands

Mon, 04 Feb 2013 04:39:01 GMT

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

Re: Conditional Queries with Data Islands

Mon, 04 Feb 2013 04:41:36 GMT

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

Re: Conditional Queries with Data Islands

maksim_senin — Mon, 04 Feb 2013 07:12:18 GMT

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

Re: Conditional Queries with Data Islands

agigliotti — Fri, 27 Jul 2018 15:53:18 GMT

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