topic Re: Range values should hold in variable in QlikView

Range values should hold in variable

jagannalla — Tue, 13 Sep 2011 07:19:19 GMT

Hi,

- In my sheet i've four inputboxes. First 2 inputboxes are able to choose date and time and when i click the button the applicaton holds data with in the range.

- Now, i want to hold one field value in 3rd inputbox. But the input box not able to hold it, for eg as shown in the below image of 2nd textobject value should hold in 3rd inutbox.

- In button action itself i written for the variable to hold this value.

For your reference i'm attaching my qvw file.

Range values should hold in variable

swuehl — Tue, 13 Sep 2011 08:38:48 GMT

Hi Jagan,

I am not sure what you are trying to achieve. To me, it seems that your button is workin.

If I change the values for the Start Lot / End Lot input boxes and then press the button, the variables are set to the value you show in the bottom text box (i.e. the first value of your concatenated list), that's what the expression of your text box and the two identical set variable actions (besides the variable name) of the button say, isn't it?

So I can't see an error, unless you are trying to achieve something else.

Regards,

Stefan

Re: Range values should hold in variable

Tue, 13 Sep 2011 09:20:13 GMT

Hi Swuehl,

I am also having the same problem

The problem is not about the range appy.The inputbox doesn't show the value with in the range default. If you click on two times the same button it is showing the minimum value with in the range and maxvalue with in the range.

Why it is happening i don't know.

Can you provide the solution for this?

Thanks..

Re: Range values should hold in variable

swuehl — Tue, 13 Sep 2011 10:12:35 GMT

Sorry, I don't just get the point:

You are selecting CaptureDateTime in the first two input boxes, right?

This will lead to implicitely selected list of distinct LotStartDateTime values, the list is printed in the first text box.

Please note that these LotStartDateTime don't necessarile fall inbetween the selected CaptureDateTime.

Then, on the same button events, you set two variables: vLotStart and vLotEnd to the same value (which is the first value in the just mentioned list. This seems to work, regardless of how many times I click the button.

(I am just using QV 10 SR2).

In the posted example, I see :

06-26-2011 23:26:42(B00BST3)

in both input boxes for Lot start and end selected.

(and if you want to select the last value in above mentioned list for the end lot date / vLotEnd variable, I think you need to replace the last subfield parameter 1 to -1 :

=SubField(Concat(DISTINCT Timestamp(LotStartDateTime,'MM-DD-YYYY hh:mm:ss')& '(' & LotNo & ')' ,','),',',-1)

Regards,

Stefan

Re: Range values should hold in variable

Tue, 13 Sep 2011 10:47:59 GMT

Hi Swuehl,

Please change the variables value like this

vLotStart=SubField(Min(Timestamp(LotStartDateTime,'MM-DD-YYYY hh:mm:ss')) & '(' & Minstring(LotNo) & ')',',',1)

vLotEnd=SubField(Max(Timestamp(LotStartDateTime,'MM-DD-YYYY hh:mm:ss')) & '(' & Maxstring(LotNo) & ')',',',1)

This is is my crrent selection

After applying this range

The first inputbox(Start Lot) showing the garbage value (like )default and the selcond inputbox(End Lot) showing the garabage value(like) But actually there it needs to show the values like ,.But if i click again that value is showing.

Please do the changes and observe why it happens.

Let me know what is the problem happening.

Thanks..

Range values should hold in variable

swuehl — Tue, 13 Sep 2011 14:14:24 GMT

I see.

Not sure why this happens.

Maybe a race condition between the first action (set selection) and the two actions setting the variable based on the selections range?

(Well, I don't really think so, but I tried using two buttons, one for the set selection in field, one for the two variable assignments, and it worked in one path. I then tried to add a macro with a sleep within the set selection in field and the two other actions, but with no success. Would be IMHO bad design of the trigger actions anyway, given that you could assign a priority / order).

But here's a at least workaround: Apply the set also for the Capturedate as set expression to your min/max functions like:

=SubField(Min({<CaptureDateTime={">=$(=vStartDate& ' ' & vStartTime)<=$(=vEndDate & ' ' & vEndTime)"} >}Timestamp(LotStartDateTime,'MM-DD-YYYY hh:mm:ss'))

& '(' & Minstring({<CaptureDateTime={">=$(=vStartDate& ' ' & vStartTime)<=$(=vEndDate & ' ' & vEndTime)"} >} LotNo) & ')',',',1)

Besides that, I am not sure if your Minstring is such a good idea in terms of performance and results, are you sure you always get the corresponding LotNo to the min LotStartDate returned (or maybe just the minstring (alphabetical ordered) )? And do you still need the subfield when using then min-functions?

Regards,

Stefan

Range values should hold in variable

swuehl — Tue, 13 Sep 2011 14:42:28 GMT

Me again,

regarding my last comment, I would probably try to get the timestamp formatting stuff out of the min functions and do the formatting on the results only. Then, as mentioned, I think I would try to use the results from the min to get a corresponding value for the LotNo.

Maybe like

=timestamp(Min({<CaptureDateTime={">=$(=vStartDate& ' ' & vStartTime)<=$(=vEndDate & ' ' & vEndTime)"} >} LotStartDateTime),'MM-DD-YYYY hh:mm:ss')

& '('& FieldValue('LotNo',FieldIndex('LotStartDateTime',Min({<CaptureDateTime={">=$(=vStartDate& ' ' & vStartTime)<=$(=vEndDate & ' ' & vEndTime)"} >} LotStartDateTime))) &')'

Seems to perform much better also (at least at my site).

Regards,

Stefan

Re: Range values should hold in variable

Tue, 13 Sep 2011 14:46:46 GMT

Swuehl,

Thanks for your reply

I need to display LotstartDateTime along with Lotno that is why i use concatenation there.

Comming to MinString,If am not using ,i am getting ()(emty brackets in the place of LotNo)Because it is not able to find Min(LotNo) and the field is of string type.I know i may not get the LotstartDateTime corresponding Lotno.

Comming to subfield,If i dont use,I am getting all the values in the same line as default.

Is there any solutions to overcome the above

Any way thans for your reply.

Re: Range values should hold in variable

Tue, 13 Sep 2011 15:18:17 GMT

Swuehl,

Thanks for your Setanalysis expression.

But how can i get the corresponding Min and Max LotNo

If you had any solution please share with me.

Thanks..

Range values should hold in variable

swuehl — Tue, 13 Sep 2011 15:22:07 GMT

Well, the expression of my last post should result in the full string (timestamp + LotNo), where I lookup the corresponding LotNo to the found minimum LotStartDateTime. I think this should be correct, don't you?

Re: Range values should hold in variable

Tue, 13 Sep 2011 15:45:18 GMT

swuehl,

Actually i didn't see your previous post and replied.But after use the latest expression, the corresponding lot is getting.

But i can't undestand the expression.

Can you please explain a bit detailed.If you do like that i may know what you had done.

Once again thanks for your reply.

Range values should hold in variable

swuehl — Tue, 13 Sep 2011 15:52:36 GMT

Sure,

=timestamp(Min({<CaptureDateTime={">=$(=vStartDate& ' ' & vStartTime)<=$(=vEndDate & ' ' & vEndTime)"} >} LotStartDateTime),'MM-DD-YYYY hh:mm:ss')

& '('& FieldValue('LotNo',FieldIndex('LotStartDateTime',Min({<CaptureDateTime={">=$(=vStartDate& ' ' & vStartTime)<=$(=vEndDate & ' ' & vEndTime)"} >} LotStartDateTime))) &')'

has two parts, first builds the timestamp string:

timestamp(Min({<CaptureDateTime={">=$(=vStartDate& ' ' & vStartTime)<=$(=vEndDate & ' ' & vEndTime)"} >} LotStartDateTime),'MM-DD-YYYY hh:mm:ss')

(I think this should be clear, finding the min LotStartDate using a set expression to limit on certain CaptureDateTimes, then formatting).

Second part

& '('& FieldValue('LotNo',FieldIndex('LotStartDateTime',Min({<CaptureDateTime={">=$(=vStartDate& ' ' & vStartTime)<=$(=vEndDate & ' ' & vEndTime)"} >} LotStartDateTime))) &')'

is doing a lookup of the LotNo directly in your fields, I am using FieldIndex('Fieldname',Fieldvalue) to retrieve the index of the just found minimum LotStartDateTime (and hopefully QV will optimize the min search, because it's already performed once) and then input this to the FieldValue('FieldName',Index) function as second parameter.

The rest is just adding brackets around.

Hope this makes it clear,

Stefan

Re: Range values should hold in variable

Wed, 14 Sep 2011 06:03:40 GMT

Swuehl,

Sorry for disturbing you again on the same post.

Please see the attached file

I have changed the data and it is not showing correct results as it is working with previous data.

Can you find what happened?

Thanks...

Re: Range values should hold in variable

swuehl — Wed, 14 Sep 2011 08:19:33 GMT

I can't see any contained data in your qvw (no tables successfully loaded?)

Please check your load. You got two FROM statements in your load. I get an error message.

Re: Range values should hold in variable

Wed, 14 Sep 2011 08:48:01 GMT

Ok Swuehl

This time you will found the correct attachment

Thanks

Range values should hold in variable

jagannalla — Wed, 14 Sep 2011 09:14:36 GMT

Thanks a lot swuehl I'll try and get back to you.

Re: Range values should hold in variable

swuehl — Wed, 14 Sep 2011 09:17:46 GMT

Your capture date time is not formatted like 'MM-DD-YYYY hh:mm:ss' ,the format you used before and is used in the actions to select on field and in set expression.

Either format your field on load or change your action expressions.

Regards,

Stefan

Re: Range values should hold in variable

Wed, 14 Sep 2011 09:45:59 GMT

Ok Swuehl,

Now how can i made that correct.

Do you have any suggestions?

Thanks..

Range values should hold in variable

swuehl — Wed, 14 Sep 2011 10:03:41 GMT

As already said (now I focus in formatting in load):

RangeData:

LOAD

timestamp(CaptureDateTime,'MM-DD-YYYY hh:mm:ss') as CaptureDateTime,

timestamp(LotStartDateTime,'MM-DD-YYYY hh:mm:ss') as LotStartDateTime,

LotNo,

Date(Floor(CaptureDateTime), 'MM-DD-YYYY') as DistinctCaptureDate

FROM

[..\2_QVDGenerator\QVD\HistoricalYears.qvd]

(qvd);

Re: Range values should hold in variable

Wed, 14 Sep 2011 10:57:28 GMT

Swuehl,

I have changed my load staement with timestamps for all fields except LotNo and tested but i didn' t get exactly.that is why i posted again.

Please see once again.

Thanks...