Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Implement Excel Formula in QlikView

Hi this is a excel formula, i want to implement  in qlikview..

requirement.PNG

So kindly help me..

Thanks in advance..

Note: Edited by Community Moderator for clarity

25 Replies
Not applicable
Author

ok

Not applicable
Author

Hi Anil,

I am sucked on  below code. Could you please look into this

IF([@[SALES]]="Non-Reportable", "Recieve",IF([@[SALES]]="Real","Real", IF(AND(LEFT([@[SALES]],10)= "Reportable",OR([@[Edit]]="System", [@[State]]="Valid")), "Real",IF(OR([@[Edit]]="System",[@[State]]=" Valid"),"Recieve", IF(AND(LEFT([@[SALES]],10)="Reportable",[@State]="QA Passed "),"Report", IF(AND (LEFT([@[SALES]], 10)="Reportable",OR( [@[Type]]="IBO",[@[Type]]="FI",[@[Type]]="Listed")), "Real", IF(AND(LEFT([@[SALES]], 10)="Reportable",OR([@[Type]]= "Individual",[@[Type]]="Active"), [@India]<>"Un",[@India]<>"CS"),"Review",IF(OR(AND([@India]<>"CS", [@India]<>"Un", OR([@[Type]]="Active",[@[Type]]= "Individual")),[@[Type]]="FI", [@[Type]]="Listed",[@[Type]]="IBO"),"Recieve",IF(OR(isnumber(search("C13",[@[Statuses]])),isnumber(search("C8",[@[Statuses]]))),"Review",IF(AND([@[SALES]]="Reportable Ent",OR(LEFT([@[Type]],1)="I", LEFT([@[Type]], 1)="H")),"Review", IF(AND([@[SALES]]="Reportable Ind", OR(LEFT([@[Type]],1)="E", LEFT([@[Type]],1)="P", LEFT([@[Type]],1)="A")),"Review", IF(AND(OR([@India]="CS",[@India]="CRS"),OR(isnumber(search("C9",[@[Statuses]])),isnumber(search("C21",[@[Statuses]])))),"Review",IF(OR([@[SALES]]="dormant", LEFT([@[SALES]], 10)="Reportable"), "Report",IF([@[Year]]=1,"Review","Check"))))))))))))))

Anil_Babu_Samineni

You can go same as i mentioned before reply

You are sending huge expression with similar format. I would suggest you to try same way in Qlik then ask me will help you for sure

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Not applicable
Author

Hi,

In this code contains AND/OR is forming in condition  i sucked to how to implement it.

Anil_Babu_Samineni

Here you go, I would suggest you to mark as Helpful

If([@[SALES]]="Non-Reportable", "Recieve",

If([@[SALES]]="Real","Real",

If(LEFT([@[SALES]],10)= "Reportable" and ([@[Edit]]="System" or [@[State]]="Valid"), "Real",

If([@[Edit]]="System" or [@[State]]=" Valid","Recieve",

If(LEFT([@[SALES]],10)="Reportable" and [@State]="QA Passed ","Report",

If(LEFT([@[SALES]], 10)="Reportable" and ([@[Type]]="IBO" or [@[Type]]="FI" or [@[Type]]="Listed"), "Real",

If(LEFT([@[SALES]], 10)="Reportable" and ([@[Type]]= "Individual" or

[@[Type]]="Active") and [@India]<>"Un" and [@India]<>"CS","Review",

If(([@India]<>"CS" and [@India]<>"Un") or ([@[Type]]="Active" or [@[Type]]= "Individual") and [@[Type]]="FI" and [@[Type]]="Listed" and [@[Type]]="IBO","Recieve",

If(IsNum(SubStringCount([@[Statuses]],'C13')) or IsNum(SubStringCount([@[Statuses]],'C18')), 'Review',

If((LEFT([@[Type]],1)="I" or LEFT([@[Type]], 1)="H") and [@[SALES]]="Reportable Ent", 'Review',

If((LEFT([@[Type]],1)="E" or LEFT([@[Type]], 1)="P" or or LEFT([@[Type]], 1)="A") and [@[SALES]]="Reportable Ind", 'Review',

If(([@India]="CS" or [@India]="CRS") and (IsNum(SubStringCount([@[Statuses]],'C9')) or IsNum(SubStringCount([@[Statuses]],'C21'))),'Review',

If([@[SALES]]="dormant" or LEFT([@[SALES]], 10)="Reportable", "Report",

If([@[Year]]=1,"Review","Check"))))))))))))))

Note: I've checked almost. Could be tiny Aestric issues. Check from your end

The red one, You can use similar to this for all. Assume, 4 Substrings are there

WildMatch([@[Statuses]],'C13', 'C18')

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Not applicable
Author

Hi,

Thanks for reply.

I have using above code,But some Close braces issue.

Could you please tell me how to rectify this?