topic Re: Selection For BookMark | table Date Field = table Date Field in QlikView

Selection For BookMark | table Date Field = table Date Field

richardouellett — Sun, 26 Feb 2017 16:26:48 GMT

Good Morning

I have a table called "Detail" that has several date fields.I would like to provide a user selection that would query the table where for example; Date_Ver = Date_Drw and if needed save this as a BookMark. I know how to select Date_Ver = 02/25/2017 and Date_Drw = 02/25/2017 but I am looking to globally apply the query Date_Ver = Date_Drw across the entire table. Can you advise?

Thanks

Rick

Re: Selection For BookMark | table Date Field = table Date Field

vishsaggi — Tue, 28 Feb 2017 16:38:42 GMT

May be create a variable and use that variable in a trigger with On Select with select in field.

LET vSelection = "= Only(IF(GetFieldSelections(Date_Drw) = Date_Ver, Date_Ver))";

And use this variable in Select in field trigger. Else

Can you share a sample and expected output you are looking for?

Re: Selection For BookMark | table Date Field = table Date Field

richardouellett — Tue, 28 Feb 2017 17:19:34 GMT

Vishwarath

Thank you for this suggestion. Per your request I attached FieldEqualField.qvw for you to work with as an example.

Rick

Re: Selection For BookMark | table Date Field = table Date Field

vishsaggi — Tue, 28 Feb 2017 17:24:12 GMT

What is your expected output should be ?

Re: Selection For BookMark | table Date Field = table Date Field

richardouellett — Tue, 28 Feb 2017 17:35:25 GMT

Current selection box would show Date_Ver = Date_Drw and in the uploaded example the data set would be limited to only 5400 records where Date_Ver = Date_Drw.

Re: Selection For BookMark | table Date Field = table Date Field

vishsaggi — Tue, 28 Feb 2017 17:38:30 GMT

Check this attached?

Re: Selection For BookMark | table Date Field = table Date Field

vishsaggi — Tue, 28 Feb 2017 17:39:25 GMT

You have mistakenly selected one of my replies as Correct Answer?

Re: Selection For BookMark | table Date Field = table Date Field

richardouellett — Tue, 28 Feb 2017 17:46:30 GMT

Well that gives the correct results but it is static. I was looking to provide a bookmark and selection capability to the end user that would limit the initial query to Date_Ver = Date_Drw and provide feedback in the current selection window.

Re: Selection For BookMark | table Date Field = table Date Field

vishsaggi — Tue, 28 Feb 2017 17:51:26 GMT

Can you elaborate on this bookmark, i quite did not get you what exactly you are trying to achieve?

Re: Selection For BookMark | table Date Field = table Date Field

richardouellett — Tue, 28 Feb 2017 19:11:45 GMT

I am not sure that I can... Basically I'm using the results in a field as criteria for selecting the contents for selection of a second field. In a script I can set a query with this criteria with the following however the Date_Drw=Date_Ver criteria is not visibly available to the end user nor can the end user invoke the criteria easily. I guess I am looking to provide a list box of date fields that can be selected by an end user to = another date field.

SET vKPISTATExcept=if(SS='x'and PrePrioity ='ST' and i1_4>1 and Date_Drw=Date_Ver ,1,0);

Re: Selection For BookMark | table Date Field = table Date Field

vishsaggi — Tue, 28 Feb 2017 19:22:37 GMT

Let us take an example from your sample file you attached. You got two dates in there, with only one date value in your Date_Ver field. What is that you want when you select Date_Ver field? You want a new listbox as mentioned in your earlier reply, what values you want in that listbox? Sorry i am trying to understand what you are trying to do here or expecting ?

Re: Selection For BookMark | table Date Field = table Date Field

richardouellett — Tue, 28 Feb 2017 19:52:12 GMT

Perhaps a bit of detail... The 'detail' table contains transactions with several date fields indicating tasks/steps of completion. 80% of the time a transaction is completed on the same day. In the example I provided it contained transactions for a single day based on 2/27/2017 for Date_Ver; the complete data set has 3 years worth of transactions. In the qvw provided the selection below only allows analysis of a single day. If the end used had the ability to select Data_Ver = Date_Drw for a data set contain several months worth of transactions it would be much preferred.

There is interest in looking at this (80%) of the work completed on same day as well as the work in progress (QV can handle this with select exclude.) My question is really about modifying the user interface to comply with how this client thinks and manages their work.

So going back to your original suggestion how could you put it into a BookMark available to an end user?

LET vSelection = "= Only(IF(GetFieldSelections(Date_Drw) = Date_Ver, Date_Ver))";

Re: Selection For BookMark | table Date Field = table Date Field

vishsaggi — Tue, 28 Feb 2017 20:26:41 GMT

Reference taken from here: Save variable values to a bookmark

Created a Input box and added two variables to be saved inside book mark based on selection.

See attached. I am not sure how we have to test this, i need more dates in Date_Ver to test the bookmark. Let me know if that is possible to populate more dates in that field.

Re: Selection For BookMark | table Date Field = table Date Field

richardouellett — Tue, 28 Feb 2017 21:13:05 GMT

Vishwarath

I appreciate you patience and effort. Several days worth of data was added to your solution and uploaded to FieldEqualsField-5.qvs per your suggestion. You have provided a user friendly solution. However I would still like to determine if QlikView will allow the creation of a BookMark when implemented that will yield Date_Ver=Date_Drw and if possible if it will display in the in the current selection window as such.

Thank you

Rick

Re: Selection For BookMark | table Date Field = table Date Field

vishsaggi — Tue, 28 Feb 2017 21:29:47 GMT

I have a bookmark already inside the qvw file i sent you earlier. I quite did not get you, correct me, when the user selects a date in Date_Ver, i can already see both the dates in current selection box. You want QV to create a Bookmark automatically based on this date selection?

Re: Selection For BookMark | table Date Field = table Date Field

vishsaggi — Tue, 28 Feb 2017 21:37:05 GMT

Check this and let me know if this is what you are looking for. I have two bookmarks created based on different date selections. So even though you come out of selections and go back and select the book mark it would display the data with bookmark saved with different dates. Let me know if i have not explained it clearly. You can find two bookmarks in bookmarks tab.

Re: Selection For BookMark | table Date Field = table Date Field

richardouellett — Tue, 28 Feb 2017 21:40:09 GMT

I may be seeking a solution using a QV Bookmark that may not be doable. Here is the criteria.

Assume a 3 year data set where Data_Ver=Date_Drw will query out 20% of the records. I would like to impose this query on the fly using a bookmark. Once imposed and the user is now looking at 80% of the records they could then select a single or multiple Date_Ver dates. Essentially the Bookmark is using a SQL statement Select * where Date_Ver = Date_Drw.

Re: Selection For BookMark | table Date Field = table Date Field

vishsaggi — Tue, 28 Feb 2017 21:49:21 GMT

So sorry I am not sure if that can be possible. But a relevant link here if that would suffice your requirement:

Does Dynamic Bookmark work?

May be some other experts can help here?

Re: Selection For BookMark | table Date Field = table Date Field

richardouellett — Tue, 28 Feb 2017 22:27:43 GMT

Thank you very much for your effort, time and patience. You certainly have helped me refine my criteria and expected output. I am looking for a Dynamic Bookmark after all and appreciate the link you provided.

Re: Selection For BookMark | table Date Field = table Date Field

vishsaggi — Tue, 28 Feb 2017 22:41:08 GMT

No problem.