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

Import external data based on filter selection.

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.

7 Replies
sunny_talwar

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

Not applicable
Author

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.

prieper
Master II
Master II

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

sunny_talwar

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.

Not applicable
Author

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!

Not applicable
Author

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.

Help Hover5.JPG

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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