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

[Anonymous]

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

[rwunderlich]

Try it using Set Analysis instead of if().

-Rob

[#4]

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

[#5]

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

Matt

[rwunderlich]

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

[Anonymous]

hi,

  it is possible to provide sample data for your requirement.

[#8]

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

[Oleg_Troyansky]

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

[Anonymous]

hi,

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

Thanks

Regards