topic Filters in QlikView

Filters

Sat, 28 May 2011 11:06:17 GMT

Hi to All,

Help needed to me from you in my requirement.

Below you can found my explanation.

I have attached my Qvw file with this post.

if Startdate,Time is selected from First Inputbox and Enddate,Time is selected from Second Inputbox and click the button then the system will display respective LotNos (Lot Numbers) along with minimum of LotDateTime.It will display all the LotNos’ and minimum of Lot start date from the Table for the selected date range. The user can select only one lot no from the “Lot Start” and “Lot End” list boxes.After selection of lots from both LotStart and LotEnd Listboxes then click on LotRange button,

The system will query the database(Table) for all the lots within LotDateTime column available in the lot start and lot end list boxes. The user can select only one option for lot start and lot end list boxes and also the system will have the validation on lot start date for lot start will be lesser than that of lot end list box. This feature allows the user to select the lot range for eliminate rows from the Table.

If the user selects lot range then the date range filter will be removed and lot start date range filter will be applied. If the user selects date range filter once again then lot range filter will be removed and capture date filter will be applied to the dataset(Table).

The selected filter will be visible to the user in current selection box.

I Think you undersatand my explanation.If you require any information regarding my requirement reply me.

Thanks.....

Filters

IAMDV — Sat, 28 May 2011 20:55:03 GMT

Sorry, please can you explain the question? or what do you want to achieve...?

Thanks - DV

Filters

Mon, 30 May 2011 05:05:18 GMT

Hi DV,

Thanks for your reply.

If after clicking of DateRange button Respective lots should be displayed along with min LotstartDateTime in the Listboxes what i have taken in the file.From that the user may select only one lot from each of the listboxes and after clicking the LotRange button(validation for this is Lotend will be greaterthan Lotstart),then DateRange filter(Button) hide and only LotRnge filter should be applied on the table.If the user selects once again the date range from the inputboxes then LotRange(Button) should be hide.and only DateRange filter should be applied on the table and current selections should be displayed in the current selection box.

In My file you can found the design how i want.and i have tried to do but i didn't complete that .Please help me in that .

Thanks....

Re: Filters

Mon, 30 May 2011 05:05:32 GMT

Hi DV,

Thanks for your reply.

In My file you can found the design how i want.and i have tried to do but i didn't complete that .Please help me in that .

Thanks....

Re: Filters

Anonymous — Mon, 30 May 2011 07:15:45 GMT

Goodmorning,

I think you have to take a look at your select field action.

First the Field you selected(CaptureDate) doesn't exsist (Must be "CaptureDateTime" I think)

Second; your expression gives an error, I think because you are using variables as if it's a field.

Let me know if this is usefull ok?

Good Luck.

Re: Filters

Mon, 30 May 2011 07:26:34 GMT

Hi Dennis,

Thanks for your reply.

All the things that i was corrected in the file . and what i am asking is about the functionality.

You can found my latest file here.

Thanks....

Re: Filters

Anonymous — Mon, 30 May 2011 07:33:30 GMT

Make sure that your date formats are the same.

The format in your data= 'DD-MM-YYYY';

But in your selections you use: 'MM-DD-YYYY';

Re: Filters

Mon, 30 May 2011 08:06:18 GMT

Hi Dennis,

Thanks for your reply,

Can you send the attachment by modifying my file.and it contains all the requirements what exactly i want those are described by me in my earlier posts.

Thanks...

Re: Filters

Anonymous — Mon, 30 May 2011 09:46:42 GMT

I am working on this but I found some strange formats in your data which makes it harder.

Here is the problem:

Your Field CaptureDateTime returns a different format then the other 2 date field.

What you can try is to add the following line to your script:

Num(Date) as DateNum,

This will return a number (like: 40689.1234567 = date.hours/minutes/seconds) which makes it easier to count with, no more different formats

I created 2 new varaibles:

vStart =NUM(vStartDate &' '& vStartTime)

vEnd =NUM(vEndDate &' '& vEndTime)

These will also return a number in the same formate as your new field DateNum.

I can not test this because I don't have your data source but maybe you can give it a try and see where you can get from here?

Re: Filters

Anonymous — Mon, 30 May 2011 10:25:55 GMT

Try using the following marco in you button:

sub SelectDatum

ActiveDocument.ClearAll false

First= ActiveDocument.Variables("vStart").GetContent.String

Last= ActiveDocument.Variables("vEnd").GetContent.String

mySelect= ">" & First & " <" & Last

ActiveDocument.Fields("datum").Select mySelect

end sub

Where "datum" is the name of your date field.
This works for me. But only if the formats are right.

Re: Filters

Mon, 30 May 2011 13:41:50 GMT

Hi Dennnis,

Thanks for your reply,

Your code is working well in another system Because i am not able to open your file(what you sent) in my system.

The same thing I have applied in my file but i am not achieving the result exactly like you.

I am attaching my file with what you suggested me to do and not able to get exact result.

Please check where i am wrong and let me know.

Thanks....

Re: Filters

Anonymous — Tue, 31 May 2011 07:52:43 GMT

Good morning,

You are on the right way.

The problem is still your format.

You have to realise that if you want to make a selection the variabel must be exacly the same format as the field you want to make your selection on.

The first thing I do when I work with variables is make them visuable.

You can do this by creating a new input box and select vEnd and vStart

As you do this, you can see these are empty in your file, this means something is wrong there to start with.

(this way you are using empty variables in your marco)

I got you last Qlikview file to work by change the following:

1. There must be a space in between the 2 qoutes in you variable:

=Num(vStartDate &' '& vStartTime)

is stead of:

=Num(vStartDate &''& vStartTime)

and:

=Num(vEndDate &' '& vEndTime)

in stead of:

=Num(vEndDate &''& vEndTime)

You need this because the original datefield also has that space between date and time

2 Go to your inputboxes and change the formats of your variables (number tab) in to the same format as the orignale datafield, which is the following:

vStartDate Date DD-MM-YYYY

vStartTime Time hh:mm:ss

vEndDate Date DD-MM-YYYY

vEndTime Time hh:mm:ss

Now fill the input boxes again in the right format and now you will see in your new inputbox that the variables are shown.

Add the field DataNum to your sheet so you can see that the format of your variables is the same as the format in your DataNum field. If so then the marco will work.

Let me know if this works, ok?

Good luck.

Re: Filters

Tue, 31 May 2011 08:57:42 GMT

Hi Dennis,

Once Again Thanks for your reply,

I made changes to the variables vStart and vEnd by providing single space between quotes and also in the macro.But It is not working .

I am attaching my file with this post and check why it is not working.

Please ,let me know by your reply.

Thanks......

Re: Filters

Anonymous — Tue, 31 May 2011 09:11:28 GMT

Your dashboard is right

Just add seconds to your time and it works.

Like I said the format must be the same so if your datafields has seconds your input field also needs to have seconds. (hh:mm:ss)

let me know if this works ok?

Re: Filters

Tue, 31 May 2011 09:56:47 GMT

Hi Dennis,

I have done the changes of what you told in your latest post still it is not working.

Here i am attaching the file what you sent me,after adding macro it isworking fine .Please check this you didn't do any modifications here,but it is working .

Let me Know where should change required exactly.

Thanks....

Re: Filters

Anonymous — Tue, 31 May 2011 10:00:04 GMT

Sorry I don't understand, you say that it is working fine, it does here too.

So what is your question?

Re: Filters

Tue, 31 May 2011 11:12:57 GMT

Hi Dennis,

Thank you Very Much and You are Too Good.

Now i Got the clear Idea and My file also working well.But a bit confusion is there to check the validations based on time also.

Is it possible to consider only (HH:MM) in Time. and also a problem happens like after clicking of DateRange button The respected Lots are displayed only on LotStart Listbox.

can we show that to effect in LotEnd ListBox also..

Let me know .

Thanks.....