topic Re: Import external data based on filter selection. in QlikView

Import external data based on filter selection.

Sat, 05 Dec 2015 17:20:28 GMT

Hi,

I want to create an external file, preferrably an Excel file, from which I can pull data based on filter selection made by the user.

Is it possible to do so?

Also let me know if I can put conditional statement in excel or other external file which will reflect the final condition in Qlikview based on filter selection, this will help in reducing the number of rows if Excel is used.

Re: Import external data based on filter selection.

sunny_talwar — Sat, 05 Dec 2015 21:56:17 GMT

Can you elaborate on what exactly you are trying to do?

Re: Import external data based on filter selection.

Sun, 06 Dec 2015 05:00:30 GMT

Suppose I have 4 users-A,B,C & D ,3 products- P,Q,R and 3 more filter selection namely Contract, Plan & Geography- let's name it 'view by'.

I have to put help text on mouse hover. As these help texts are to be provided at multiple places based on above mentioned filter so I am thinking to bring these help texts from external file for easy updation and easy to keep track of.

The help text should change when view by is changed or a particular combination of filter is selected like 'View by- User A- Product Q' or 'View by- User B'.

Currently I have implemented it using conditional statements in the help text under caption menu of text box properties. The condition filters the result based on above mentioned filters.

Example-

=if(product='P','Data Source: XYZ', 'Data Source: R')

or a more complex one

=if(View='Contract' and product='P','Data Source: MN',

if(View='Contract' and product<>'P','Data Source: EF',

if (View='Plan' and product='P','Data Source: GH',

if(View='Plan' and product=Q','Data Source: IJ',

if(View='Geography' and product='P','Data Source: Sales-KL, Lives-DE',

'Data Source: XY')))))

Now I am trying to put the final text to be displayed in an external file and trying to pull it based on the filter selection made by the end user.

Let me know if it helps.

Re: Import external data based on filter selection.

prieper — Sun, 06 Dec 2015 09:36:17 GMT

have you checked the WHERE-clause?

LOAD * FROM MyExcel (Parameters) WHERE Field = 'MyValue';

Instead of the fixed string you may also define a variable:

LOAD * FROM MyExcel (Parameters) WHERE Field = '$(MyVariable)';

HTH Peter

Re: Import external data based on filter selection.

sunny_talwar — Sun, 06 Dec 2015 13:10:08 GMT

So are you going to reload the application after the users make their selection? Cause I don't see how you can conditionally get stuff from an excel file without doing a reload or (may be partial reload). If reloading/Partial Reloading is an option then this might be possible to do using the where statements as pointed out by Peter below.

Re: Import external data based on filter selection.

Sun, 06 Dec 2015 14:20:47 GMT

Hi,

I am not looking to load the entire application again based on user selection as my app is viewed through the browser.

Is there a way to load the complete excel file once and then assigning the corresponding rows based on user selection.

Suppose if I can assign row 1 to some particular selection from above selection and then row 2 to other selection and so on.

This means that is there a way to map rows of an excel or some other file to particular filter selection in qlikview!

Re: Import external data based on filter selection.

Sun, 06 Dec 2015 14:50:37 GMT

Hi Peter,

For this method I will have to reload the application again, right!

If so then I am looking forward to this method.

My idea here is to map each row (if not conditionally also) in such a way that I can assign the value in each row to a particular combination of filter selection and the row will always be mapped to that filter selection and will display the row's value accordingly.

As can be seen from the image the text in yellow box- help text in caption menu under text box properties changed based on whether View by is Contract or Geography.

I need to write these texts in an external file and then try to import from there. Let me know if this helps.

The complete text written in help text is-

=if(View='Contract' and MARKET_NAME='Immunology','Data Source: Sales- Rebates, Lives- IHS' &chr(10)&' Access Details- BOT Coverage Policy' & chr(10)&' %Lives- BOT Backbone(Formulary)',

if(View='Contract' and MARKET_NAME<>'Immunology','Data Source: Sales- Rebates, Lives- IHS' &chr(10)&' Access Details & %Lives- BOT Backbone(Formulary)',

if (View='Plan' and MARKET_NAME='Immunology','Data Source: Sales- IMS, Lives- BOT Backbone' &chr(10)&' Access Details- BOT Coverage Policy' & chr(10)&' %Lives- BOT Backbone(Formulary)',

if(View='Plan' and MARKET_NAME<>'Immunology','Data Source: Sales- IMS, Lives- BOT Backbone' &chr(10)&' Access Details & %Lives- BOT Backbone(Formulary)',

if(View='Geography' and MARKET_NAME='Immunology','Data Source: Sales- INIMS, Lives- BOT Backbone'&chr(10)&' Access Details- BOT Coverage Policy' & chr(10)&' %Lives- BOT Backbone(Formulary)',

'Data Source: Sales- IMS, Lives- BOT Backbone' &chr(10)&' Access Details & %Lives- BOT Backbone(Formulary)')))))

These texts are not always this big but this is the biggest, there are help texts without if statements also.

Regards

Re: Import external data based on filter selection.

Peter_Cammaert — Sun, 06 Dec 2015 15:23:12 GMT

Load all help texts for all combinations of User/Product/View. Then in your help text pop-up or text box, use an expression that pulls the HelpText field value that corresponds to this particular combination of selections from the associative database. For this to work, you'll have to create a composite key (e.g. like 'ViewA|ProductB|UserC'). Let QV do the work it is designed to do.

You may have to devise a strategy for handling multiple selections in one of the fields (e.g. like User="A" and Product="X,Y,Z").Or use the Only() function if you decide not to show anything more than the first combination, or nothing at all when multiple selections were made in the same field.

Best,

Peter