Required Formula in Qlikview

[#1]

Dear All

I need Formula in Qlikview which is to be derived using Excel Formula.

Urgent Help required. No one has given correct answer for this Scenario.

[#2]

Is it Clear now?

[jonathandienst]

This formula is a little problematic. You can reference the previous row (so reference A4, C4 etc from row 5). You will need to save the value in $D1 in a variable (lets call it vD1 for example):

    If(A5 = Previous(A5) and C5 <> vD1, ...

But there is no simple, one-step way to forward-reference a row that has not yet been loaded (A6), and so you will need to do this in steps. You can for example, first load the data in reverse order so A6 becomes the Previous() value to A5, and set a flag fr that condition.

If you do this in the front end, then you can forward reference in a table using Below(), but there is no equivalent in script.

[#4]

Thanks for the Reply Sir.

I agree with you sir.

But then how do I create such data.

It's a requirement.

Provide some workaround.

[sunny_talwar · Accepted solution]

Check this out

Table:

LOAD RowNo() as Sort,

  Product,

    TranDate,

    Flag,

    Credit,

    If(Product = Peek('Product'), 1, 0) as Flag1,

    If(Flag = 'Yes', 1, 0) as Flag2

FROM

[Required Formula in Qlikview.xlsx]

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

Where Len(Trim(Credit)) > 0;

NewTable:

LOAD *,

  If(Product = Peek('Product'), 1, 0) as Flag3

Resident Table

Order By Sort Desc;

DROP Table Table;

FinalTable:

LOAD *,

  Alt(If(Flag1 = 1 and Flag2 = 0, Peek('ReqCol'),

  If(Flag2 = 1, Credit,

  If(Flag3 = 0, Peek('ReqCol')))), 0) as ReqCol

Resident NewTable

Order By Sort;

DROP Table NewTable;

[#6]

sunindia‌ Kindly provide a solution.

[sunny_talwar]

I just did propose a solution, check it out

[#8]

Thanks Sunny Sir

This is exactly what I want.

Regards

Eric

[sunny_talwar]

Great

I am glad we finally were able to resolve the issue. Do you mind closing your other thread as well. May be add a link to this thread so that someone looking into a similar thing can come to this thread?

Best,

Sunny