Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Required Formula in Qlikview

Dear All

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

Required Formula in Qlikview.png

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

1 Solution

Accepted Solutions
sunny_talwar

Check this out

Capture.PNG

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;

View solution in original post

17 Replies
Not applicable
Author

Anyone?

sunny_talwar

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)))

Not applicable
Author

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)))

sunny_talwar

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)))


vs


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

Not applicable
Author

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.



PrashantSangle

Hi,

Are you sure this time expression is correct??

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

Kind Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
sunny_talwar

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

Capture.PNG

Not applicable
Author

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

Not applicable
Author

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