Querying dataset in qlik

[#1]

I am relatively new to Qlik, but I have a question about querying data, and I think it may be impossible.

I have a list of a million...let's call them "accounts".  and in access I have this query that I wrote that brings that list down to 50,000.  Is there a way I can import this list of 50,000 records and have it query against my list of a million accounts in qlik in order to bring back 50,000 records in qlik?  The only think I see in qlik in order to query data is the list box.

Let me know your thoughts.  I appreciate any feedback.

[ogster1974]

Would be good if you posted a small subset of your data to illustrate your question.

Qlik will certainly handle your data volume and as long as you can associate the data in your 50k dataset with your 1m data set with a key then you can display your data in many chart types not just a list box.

[rwunderlich]

Is this an on-demand kind of thing? That is, do you need to be able to generate the 50k list and quickly apply the filter in Qlik Sense?  Or is this more like a daily process that builds the 50k list that can be  loaded by the QS load script?

-Rob

[#4]

let me give a little more detail.  Let's say it's auto insurance, and I have a list of 1 million auto policies, but I only want to see the data in qlik for those autos who have had 2 accidents.  I run a query in access with my original data and find that only 50,000 of those million have had 2 accidents.

So now I want to go into qlik and be able to filter on those 50,000 policies.  Is there a way to do this?

[Josh_Good]

Hi Tom,

With Qlik, generally speaking, you will load all your data in and then filter from there.  This gives you much faster filtering and also makes it possible to leverage the associative model (see: this video for a quick overview).

For your case what you can do is load all million policies with the accident information  Then create a visualization (bar chart, listbox, pivot table etc.) that shows the number of accidents.  Every visualization is interactive so you can select on everything with 2 accidents. 

With Qlik, the process is:

1. Load the data you are interested in analysing

2. Create some visualizations

3. Filter and select to do do the analysis. 

By using this approach vs pre-filtering your data, your Qlik app will be able to answer many more questions quickly vs just answer one question at a time.

-Josh

Qlik

[#6]

Josh thanks for your answer, but what if my data in qlik does not have number of accidents?  I do not load the data in qlik, I do not have those capabilities, I use a table that is published for me. 

[Josh_Good]

Hi Tom,

If your data doesn't have the number of accidents then how can you get to the data with only two accidents?  Can you provide an example show the data you have (even a simple mock up of a few lines)?

-Josh

Qlik

[rwunderlich]

I'm with Josh that it would be great to do this in QlikView. But also understand that you don't have the filtering criteria available in QV and you can't modify it. If that's absolutely the case, you can select multiple values in a Listbox with a argument in this syntax:

(2|5|10)

where 2,5,&10 are the values you want to select.  So if you can output the format of your access query output in this format, you may be able to paste it to the listbox and select all 50k values. I say "may" because I've never tried it with a list that large and I wouldn't be surprised if there were problems.

I encourage you to pursue getting the additional data -- such as accidents -- into QV if at all possible. Can you post your current data model?  (Ctrl-t and then export the image)

-Rob

http://masterssummit.com

http://qlikviewcookbook.com