topic Script logic required in QlikView

Script logic required

Kushal_Chawda — Tue, 20 Sep 2016 12:41:10 GMT

Hi All,

Need to do calculation as per the attached excel. As formula works in circular fashion , I am not sure how to do it in QlikView.

I want to do it in script only

gwassenaar‌ stalwar1‌ swuehl‌

Re: Script logic required

jonathandienst — Tue, 20 Sep 2016 13:00:18 GMT

It would be better to describe what you are trying to achieve (and show/explain the Excel formula) and the expected results, rather than requiring people to try and uncover that information to help you. The people providing help here are not Qlik employees (for the most part) and are providing this (free) service voluntarily. If you make it hard to help you, you will be less likely to get useful advice.

Re: Script logic required

Kushal_Chawda — Tue, 20 Sep 2016 14:31:08 GMT

Hi Jonathan,

Thanks for the reply. I am not sure how I will be able to explain the formula here which will be difficult to explain, but in excel there is already formula applied and required output is there. I thought this will be easy instead. Sorry for the inconvenience.

Re: Script logic required

sunny_talwar — Tue, 20 Sep 2016 15:10:01 GMT

Try this:

Table:

LOAD RowNo() as SNo,

Date(Date) as Date,

Time([Release Time]) as [Release Time],

[Release Qty],

[Progressive release],

Delivery,

Time([Portal Required Time]) as [Portal Required Time],

[Portal Required Qty],

If([Portal Required Time] > [Release Time] and Delivery > If([Release Qty] - Alt(Peek('AB'), 0) < 0, 0, [Release Qty] - Alt(Peek('AB'), 0)),

If([Release Qty] - Alt(Peek('AB'), 0) < 0, 0, [Release Qty] - Alt(Peek('AB'), 0)),

If([Portal Required Time] > [Release Time] and Delivery <= If([Release Qty] - Alt(Peek('AB'), 0) < 0, 0, [Release Qty] - Alt(Peek('AB'), 0)),

Delivery)) as [Delivery R],

RangeSum(

If([Portal Required Time] > [Release Time] and Delivery > If([Release Qty] - Alt(Peek('AB'), 0) < 0, 0, [Release Qty] - Alt(Peek('AB'), 0)),

If([Release Qty] - Alt(Peek('AB'), 0) < 0, 0, [Release Qty] - Alt(Peek('AB'), 0)),

If([Portal Required Time] > [Release Time] and Delivery <= If([Release Qty] - Alt(Peek('AB'), 0) < 0, 0, [Release Qty] - Alt(Peek('AB'), 0)),

Delivery)), Alt(Peek('Prog Del R'), 0)) as [Prog Del R],

If([Release Qty] - Alt(Peek('AB'), 0) < 0, 0, [Release Qty] - Alt(Peek('AB'), 0)) as [To be Deliver],

RangeSum(

If([Portal Required Time] > [Release Time] and Delivery > If([Release Qty] - Alt(Peek('AB'), 0) < 0, 0, [Release Qty] - Alt(Peek('AB'), 0)),

If([Release Qty] - Alt(Peek('AB'), 0) < 0, 0, [Release Qty] - Alt(Peek('AB'), 0)),

If([Portal Required Time] > [Release Time] and Delivery <= If([Release Qty] - Alt(Peek('AB'), 0) < 0, 0, [Release Qty] - Alt(Peek('AB'), 0)),

Delivery)), Alt(Peek('Prog Del R'), 0), -[Progressive release]) as [AB]

FROM

(ooxml, embedded labels, header is 1 lines, table is Sheet1)

Where Len(Trim(Date)) > 0;