topic Do While loop in QlikView

Do While loop

Tue, 09 Sep 2014 16:25:58 GMT

I have ship dates that the user can select and as they select it changes the values of another field in a chart. The goal is for this field value to be equal or greater than another value, which is calculated. The user has requested that I automate this process so when the calculated field value is set, the ship dates are advanced until the chart field is equal to or just greater than the calculated field. Can anyone assist me with the script that would do this?

Message was edited by: James Walsh

Re: Do While loop

JonnyPoole — Tue, 09 Sep 2014 16:51:03 GMT

A sample would help. Can u share a QVW ?

Re: Do While loop

Tue, 09 Sep 2014 16:55:53 GMT

How do I share it? Is it as simple as copy and paste?

James F. Walsh Jr.

MIS

ME Elecmetal

(218) 213-9571

Re: Do While loop

JonnyPoole — Tue, 09 Sep 2014 16:57:11 GMT

In your reply , go into the 'advanced editor' and from there you can attach a file.

Re: Do While loop

Tue, 09 Sep 2014 17:07:17 GMT

When I go into my message and select reply, I do not see an "Advanced

Editor"?

James F. Walsh Jr.

MIS

ME Elecmetal

(218) 213-9571

Re: Do While loop

Tue, 09 Sep 2014 17:37:28 GMT

I thought I figured out how to attach but I do not see it?

Re: Do While loop

Tue, 09 Sep 2014 17:58:15 GMT

It is attached to the original entry.

Re: Do While loop

Tue, 09 Sep 2014 18:40:02 GMT

"Net Tons to Pour", based on user values for "Net Tons per Day to Pour" and "# of Pour Days", is calculated and when "Net Good Tons" is equal to or greater than "Net Tons to Pour", "Pour Date" selection should stop. Currently, once I have a value for "Net Tons to Pour", I start selecting "Pour Date" and guess when "Net Good Tons" might be close to "Net Tons to Pour". The user has asked if I can automate this so once "Net Tons to Pour" is calculated, "Pour Date"s are selected until "Net Good Tons" is no long less than "Net Tons to Pour".

Re: Re: Do While loop

JonnyPoole — Wed, 10 Sep 2014 00:19:01 GMT

I changed the 'Pour Date' list box to an expression;

=if ( (TonsperDay*PourDays) <= aggr( Sum ((([Each Weight])/2000)*[To Pour Qty.]), [Pour Date]), [Pour Date])

It means that [Pour Date] will only show if 'Net Tons to Pour' is less than or equal to 'Net Good Tons'

I also added a list box expression whic shows 'net good tons' so the user has an idea of the threshold and what further filtering may yield.

also attached is the QVW sample. Did i get this right ? I couldn't be 100% after reading your explanation. ! !

Re: Re: Do While loop

Wed, 10 Sep 2014 16:01:33 GMT

But I still need to show Net Good Tons broken out by Metal Type (Alloy). Do I need to add the Pour Date expression to my to charts that are already on the page?

Re: Re: Do While loop

Wed, 10 Sep 2014 16:23:50 GMT

I tried adding your expression to "Tons to Pour by Alloy" chart but the expression comes back null. I need the application to perform as it does now except automate the Pour Date selection to stop when Net Good Tons is = or > the goal of Net Tons to Pour.

Re: Re: Do While loop

Wed, 10 Sep 2014 16:34:25 GMT

See my new attachment for an example of the needed outcome. Thanks!

Re: Re: Do While loop

Thu, 11 Sep 2014 16:47:30 GMT

Is what I asking for possible (see new messages and updated attachment)?

Re: Re: Re: Do While loop

JonnyPoole — Thu, 11 Sep 2014 20:16:23 GMT

Hi - i didn't see any new attachments in the recent posts and i am not certain what the described need is. To improve communication i've pasted below an attempt of what i think you are asking for. I think you are asking for #3 below... to only show rows that exceed the number computed on the right . Can you please clarify with screenshots / explanations as below ? thank you !

Re: Do While loop

Thu, 11 Sep 2014 20:36:09 GMT

I edited the first posting with an attachment that shows what I am trying to do. Can you not see that attachment any longer?

Re: Do While loop

Thu, 11 Sep 2014 20:39:43 GMT

You should be able to see the attachment if go to the Discuss Forum and click on my posting(s).

Thanks!

Re: Re: Re: Do While loop

Mon, 15 Sep 2014 18:03:56 GMT

Jonathan - Can you see the attachment I am speaking of? If so, can this be done in QlikView?

Thanks!

Re: Do While loop

JonnyPoole — Mon, 15 Sep 2014 18:17:06 GMT

Hi James - i saw a description in a text box on the first tab. I sort of understood it but needed to spend more time on it . I got caught up on a few things but when i get back to my list of 'more intensive community threads' this is in my top 3.

Re: Do While loop

Mon, 15 Sep 2014 21:07:12 GMT

Thanks again.

Re: Do While loop

JonnyPoole — Thu, 18 Sep 2014 00:47:59 GMT

James i think this is possible but the way i see it , the model needs a new table that would track dates 'cumulatively'.

Say the dates in my chart were

Date

1/1/2014

1/2/2014

1/3/2014

I would create a new table with Date and CumulativeDateBucket like this:

CumulativeDateBucket Date

1 1/1/2014

2 1/1/2014

2 1/2/2014

3 1/1/2014

3 1/2/2014

3 1/3/2014

so that i have a bucket that will map in all the previous dates.

then i'm thinking of a SET ANALYSIS statement that would return the set of cumulativedatebucket's that have a sum( NETGOODTONS) > 'the user inputed calculated result'. You are allowed to do this in SET ANALYSIS (use aggregation functions in the set modifier) its in the help.

And the set expression would go within a min() function that returns the smallest cumulativedatebucket . That cumulativedatebucket value would be selected through a 'select in field' action button that the user hits and it will indirectly select all the dates up to that datebucket.

sounds great but i don't have a ready sample for you ! can you go on this for a bit ? I'm tapped out until next week