Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Conditional Queries with Data Islands

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:

Screen Shot 2013-01-31 at 3.39.36 PM.png

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

13 Replies
Anonymous
Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

Try it using Set Analysis instead of if().

-Rob

Not applicable
Author

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

Not applicable
Author

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

Matt

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Not applicable
Author

hi,

  it is possible to provide sample data for your requirement.

Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Not applicable
Author

hi,

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

Thanks

Regards