topic Re: On-Demand Data Pull in QlikView

On-Demand Data Pull

johnjustus — Wed, 25 Nov 2020 16:16:04 GMT

I have an requirement like this.

I have an Listbox that contains list of dates.

When the user opens the QVW from AccessPoint, they will be presented just with the list box populated with list of dates (eg., Jan 2017, Feb 2017 and so on..) And the user will be displayed with an empty table (Date, Amount etc.,)

Now when the user picks a date from the Listbox, the empty table should be populated with data for the corresponding selected date.

How can I achieve this in Qlikview?

I am using Version : 11.20.12354.0 SR6

Thanks

Re: On-Demand Data Pull

neelamsaroha157 — Wed, 03 May 2017 15:45:58 GMT

You can use calculated condition in your chart.

Something like in the attached file.

Re: On-Demand Data Pull

johnjustus — Wed, 03 May 2017 15:53:24 GMT

Hi Neelam,

Thanks for the information.

In your case, you are using an single table.

Mine is slightly different.

Lets say, I have one query as below which would bring in only the dates.

Select * from Date table

My another query would be the main table.

Select * from fact table

An join exists between these 2 tables on a field.

In terms of data display, I want to display the chart after I select the date from the list box. I don't want to pre-load the data rather I want to send live query for the selected date and then populate the chart with data

Is it possible?

Thanks

Re: On-Demand Data Pull

neelamsaroha157 — Wed, 03 May 2017 16:14:19 GMT

Are you trying to achieve this via scripting?

If you have all the data loaded, i guess you'll get the same result as you are getting from your query.

The chart condition will pass on the date selection to you main table which you are trying to display in your chart.

Re: On-Demand Data Pull

johnjustus — Wed, 03 May 2017 16:31:59 GMT

Yes, scripting solution is fine.

Using an variable, I think we can pass a parameter to the second query and then the second query can bring in the data?

Theoretically I can think as I am new to Qlikview not sure how to implement.

If you can take that idea and put that in to QVW using 2 different spreadsheet that would be helpful if that approach make sense.

Thanks

Re: On-Demand Data Pull

neelamsaroha157 — Wed, 03 May 2017 16:36:14 GMT

If you are passing values from a front end variable to script, then everytime you change the value, you'll have to reload data and it is not good idea to give permissions to end user for reload.

If you can share some sample data and the exact output, then it would be much easier to help .

Re: On-Demand Data Pull

Digvijay_Singh — Wed, 03 May 2017 16:56:43 GMT

It doesn't make difference whether you have single table or two tables as long as tables are associated through some field. I am afraid if you want to pull data from your source database directly in front end. ( In qlikview the usual process is to reload first, the data from sources in the data model and then use it in the front end, All the data always available with the application after reload).

You always reload your application when you need updated data from your source databases.

You would need to use direct discovery features(Direct Query statement) if you are expecting automatic new data loading from your DW DB without manual reload.

The sample attached is based on two tables and show condition in the straight table.

Re: On-Demand Data Pull

johnjustus — Wed, 03 May 2017 17:16:04 GMT

Here is the attached dataset and also it shows the output what I am looking for.

The script can contain query or reference to both the Month table and the Fact table, however fact table should load only after selecting the month from the list box.

Yes, I understand that reload option should not permissioned to end users.

Please take a look and let me know how we can approach.

I appreciate your time sincerely.

Re: On-Demand Data Pull

johnjustus — Wed, 03 May 2017 17:28:00 GMT

Thanks for your time Vijay!

Agree on the usual process of Qlikview.

It looks like then there is no other way of achieving this req with out Direct Discovery.

Do you perhaps have an sample how it will work using Direct Discovery?

If you can give me an sample script then I will use 2 different excel files (Data on 1 file will be pre-loaded and data on the fact excel file will be loaded as on-demand) and will test how Direct Discovery works.

Thanks Again!

Re: On-Demand Data Pull

Wed, 03 May 2017 17:28:35 GMT

You can try direct discovery option

Re: On-Demand Data Pull

Wed, 03 May 2017 17:35:06 GMT

Or you can used Calculation Condition in Chart Properties (General Tab).

Like GetSelectedCount(DateField)=1

Re: On-Demand Data Pull

Digvijay_Singh — Wed, 03 May 2017 17:38:23 GMT

I know the concept only, you can take help from attached doc, but I suggest use some traditional way to meet your requirement as long as it can be met through standard Qlikview methods

Re: On-Demand Data Pull

johnjustus — Wed, 03 May 2017 17:41:00 GMT

Ok, Thanks Vijay!

What would be the effect of using variable / Parameter in the query?

Would that accomplish what I am looking for? However pre-load is necessary, correct?

Re: On-Demand Data Pull

Digvijay_Singh — Wed, 03 May 2017 18:30:28 GMT

You need to keep your list box field associated with the fields you want to update in your main table. You don't need to pass it on in your real query. Direct query will help in fetching latest data from your original source. You have to use calculation condition or show condition in the final user interface object/table, if you want to show data only when any date is selected in the list box. Its mandatory to have association with DATE and other output fields in some way else it won't understand what to display in your main table. I think pre-load is necessary, I never used it so cannot comment much on direct query.