topic Re: Required Formula in Qlikview in QlikView

Required Formula in Qlikview

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Required Formula in Qlikview

Mon, 25 Apr 2016 12:21:38 GMT

Anyone?

Re: Required Formula in Qlikview

sunny_talwar — Mon, 25 Apr 2016 12:23:43 GMT

Is the formula this?

IF(AND(A4=A3,NOT(C4=D$1)),E3,IF(C4=D$1,D4,IF(NOT(A4=E5),E3,0)))

or this?

IF(AND(A4=A3,NOT(C4=D$1)),E3,IF(C4=D$1,D4,IF(NOT(A4=A5),E3,0)))

Re: Required Formula in Qlikview

Mon, 25 Apr 2016 12:44:43 GMT

Oh Sorry. I did Silly mistake.

This is the Formula.

IF(AND(A4=A3,NOT(C4=D$1)),E3,IF(C4=D$1,D4,IF(NOT(A4=A5),E3,0)))

Required Formula in Qlikview

Re: Required Formula in Qlikview

sunny_talwar — Mon, 25 Apr 2016 12:49:13 GMT

Are you sure there are no other silly mistakes? Such as

IF(AND(A4=A3,NOT(C4=D$1)),E3,IF(C4=D$1,D4,IF(NOT(A4=A5),E3,0)))

IF(AND(A4=A3,NOT(C4=D$1)),E3,IF(C4=D$1,D4,IF(NOT(A4=A5),E5,0)))

Re: Required Formula in Qlikview

Mon, 25 Apr 2016 13:05:21 GMT

This is the Final Formula I need.

IF(AND(A5=A4,NOT(C5=D$1)),E4,IF(C8=D$1,D5,IF(NOT(A5=A6),E4,0)))

I did a lot of Silly Mistakes.

I have also corrected the Original Post Data.

Re: Required Formula in Qlikview

PrashantSangle — Mon, 25 Apr 2016 13:18:30 GMT

Hi,

Are you sure this time expression is correct??

On 5th row you want to compare flag of row 8...

Kind Regards,

Re: Required Formula in Qlikview

sunny_talwar — Mon, 25 Apr 2016 13:54:18 GMT

This looks awkward. Are you sure the formula is correct?

Re: Required Formula in Qlikview

Mon, 25 Apr 2016 15:27:54 GMT

Sorry Again

I have corrected the Post with updated Attached Excel and Snapshot.

This is the Correct Formula:

IF(AND(A5=A4,NOT(C5=D$1)),E4,IF(C5=D$1,D5,IF(NOT(A5=A6),E4,0)))

I want this is Qlikview Script.

Sorry for the inconvenience caused.

Regards

Eric

Re: Required Formula in Qlikview

Mon, 25 Apr 2016 15:28:04 GMT

Sorry Again

I have corrected the Post with updated Attached Excel and Snapshot.

This is the Correct Formula:

IF(AND(A5=A4,NOT(C5=D$1)),E4,IF(C5=D$1,D5,IF(NOT(A5=A6),E4,0)))

I want this is Qlikview Script.

Sorry for the inconvenience caused.

Regards

Eric

Re: Required Formula in Qlikview

Tue, 26 Apr 2016 02:53:28 GMT

Is it Clear now?

Re: Required Formula in Qlikview

jonathandienst — Tue, 26 Apr 2016 05:04:12 GMT

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.

Re: Required Formula in Qlikview

Tue, 26 Apr 2016 07:17:49 GMT

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.

Re: Required Formula in Qlikview

sunny_talwar — Tue, 26 Apr 2016 09:11:37 GMT

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;

Re: Required Formula in Qlikview

Tue, 26 Apr 2016 09:12:08 GMT

sunindia‌ Kindly provide a solution.

Re: Required Formula in Qlikview

sunny_talwar — Tue, 26 Apr 2016 09:12:46 GMT

I just did propose a solution, check it out

Re: Required Formula in Qlikview

Wed, 27 Apr 2016 10:02:18 GMT

Thanks Sunny Sir

This is exactly what I want.

Regards

Eric

Re: Required Formula in Qlikview

sunny_talwar — Wed, 27 Apr 2016 10:07:16 GMT

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