Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Creator III
Creator III

Re: Conditional Queries with Data Islands

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: Conditional Queries with Data Islands

Try it using Set Analysis instead of if().

-Rob

Highlighted
Not applicable

Re: Conditional Queries with Data Islands

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

Highlighted
Not applicable

Re: Conditional Queries with Data Islands

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

Matt

Highlighted
MVP & Luminary
MVP & Luminary

Re: Conditional Queries with Data Islands

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

Highlighted
Creator III
Creator III

Re: Conditional Queries with Data Islands

hi,

  it is possible to provide sample data for your requirement.

Highlighted
Not applicable

Re: Conditional Queries with Data Islands

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: Conditional Queries with Data Islands

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

Highlighted
Creator III
Creator III

Re: Conditional Queries with Data Islands

hi,

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

Thanks

Regards