Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP 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

1 Solution

Accepted Solutions
Anil_Babu_Samineni

May be this?

If(IsNum(SubStringCount([@[Statuses]],'P3') or IsNum(SubStringCount([@[Statuses]],'C6')), 'Pending',

If(IsNum(SubStringCount([@[Statuses]],'P6') or IsNum(SubStringCount([@[Statuses]],'C12') or IsNum(SubStringCount([@[Statuses]],'C14') or IsNum(SubStringCount([@[Statuses]],'C18') or IsNum(SubStringCount([@[Statuses]],'C19')), 'Repair',

If(IsNum(SubStringCount([@[Statuses]],'C3') or IsNum(SubStringCount([@[Statuses]],'P8')), 'Dormant',

If(IsNum(SubStringCount([@[Statuses]],'P7') or IsNum(SubStringCount([@[Statuses]],'P9')), 'Reportable Ind',

If(IsNum(SubStringCount([@[Statuses]],'C8') or IsNum(SubStringCount([@[Statuses]],'C9') or IsNum(SubStringCount([@[Statuses]],'C13') or IsNum(SubStringCount([@[Statuses]],'C20') or IsNum(SubStringCount([@[Statuses]],'C21') or IsNum(SubStringCount([@[Statuses]],'C22')), 'Reportable ENT', 'Non-Reportable')))))

Or This? By using IsNum function instead of IsNumber in qlikview

If(WildMatch([@[Statuses]],'P3*') or WildMatch([@[Statuses]],'C6*'), 'Pending',

If(WildMatch([@[Statuses]],'P6*') or WildMatch([@[Statuses]],'C12*') or WildMatch([@[Statuses]],'C14*') or WildMatch([@[Statuses]],'C18*') or WildMatch([@[Statuses]],'C19*'), 'Repair',

If(WildMatch([@[Statuses]],'C3*') or WildMatch([@[Statuses]],'P8*'), 'Dormant',

If(WildMatch([@[Statuses]],'P7*') or WildMatch([@[Statuses]],'P9*'), 'Reportable Ind',

If(WildMatch([@[Statuses]],'C8*') or WildMatch([@[Statuses]],'C9*') or WildMatch([@[Statuses]],'C13*') or WildMatch([@[Statuses]],'C20*') or WildMatch([@[Statuses]],'C21*') or WildMatch([@[Statuses]],'C22*'), 'Reportable ENT', 'Non-Reportable')))))


Or you can add haiku(*) before of the search string as well

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

25 Replies
vishsaggi
Champion III
Champion III

Can you copy paste the whole script as text rather than an image?

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Can you explain what the business logic is behing this mass of braces? That may make translation into QlikView easier...

Not applicable
Author

By using this formula i need to create a new field in qlikview. i.e., Product or etc.. (This is already implemented in excel)

=IF(OR(isnumber(search("P3",[@[Statuses]])),isnumber(search("C6",[@[Statuses]]))),"Pending",IF(OR(isnumber(search("P6",[@[Statuses]])),isnumber(search("C12",[@[Statuses]])),isnumber(search("C14",[@[Statuses]])),isnumber(search("C18",[@[Statuses]])),isnumber(search("C19",[@[Statuses]]))),"Repair",IF(OR(isnumber(search("C4",[@[Statuses]])),isnumber(search("P8",[@[Statuses]]))),"Dormant",IF(OR(isnumber(search("P7",[@[Statuses]])),isnumber(search("P9",[@[Statuses]]))),"Reportable Ind",IF(OR(isnumber(search("C8",[@[Statuses]])),isnumber(search("C9",[@[Statuses]])),isnumber(search("C13",[@[Statuses]])),isnumber(search("C20",[@[Statuses]])),isnumber(search("C21",[@[Statuses]])),isnumber(search("C22",[@[Statuses]]))),"Reportable ENT","Non-Reportable")))))

Anil_Babu_Samineni

Formula is more sufficient to reply, It would better if you provide what you are expecting and at least business. So then we can understand easily what they want in simpler way by  help of Qlik.

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anil_Babu_Samineni

May be this?

If(IsNum(SubStringCount([@[Statuses]],'P3') or IsNum(SubStringCount([@[Statuses]],'C6')), 'Pending',

If(IsNum(SubStringCount([@[Statuses]],'P6') or IsNum(SubStringCount([@[Statuses]],'C12') or IsNum(SubStringCount([@[Statuses]],'C14') or IsNum(SubStringCount([@[Statuses]],'C18') or IsNum(SubStringCount([@[Statuses]],'C19')), 'Repair',

If(IsNum(SubStringCount([@[Statuses]],'C3') or IsNum(SubStringCount([@[Statuses]],'P8')), 'Dormant',

If(IsNum(SubStringCount([@[Statuses]],'P7') or IsNum(SubStringCount([@[Statuses]],'P9')), 'Reportable Ind',

If(IsNum(SubStringCount([@[Statuses]],'C8') or IsNum(SubStringCount([@[Statuses]],'C9') or IsNum(SubStringCount([@[Statuses]],'C13') or IsNum(SubStringCount([@[Statuses]],'C20') or IsNum(SubStringCount([@[Statuses]],'C21') or IsNum(SubStringCount([@[Statuses]],'C22')), 'Reportable ENT', 'Non-Reportable')))))

Or This? By using IsNum function instead of IsNumber in qlikview

If(WildMatch([@[Statuses]],'P3*') or WildMatch([@[Statuses]],'C6*'), 'Pending',

If(WildMatch([@[Statuses]],'P6*') or WildMatch([@[Statuses]],'C12*') or WildMatch([@[Statuses]],'C14*') or WildMatch([@[Statuses]],'C18*') or WildMatch([@[Statuses]],'C19*'), 'Repair',

If(WildMatch([@[Statuses]],'C3*') or WildMatch([@[Statuses]],'P8*'), 'Dormant',

If(WildMatch([@[Statuses]],'P7*') or WildMatch([@[Statuses]],'P9*'), 'Reportable Ind',

If(WildMatch([@[Statuses]],'C8*') or WildMatch([@[Statuses]],'C9*') or WildMatch([@[Statuses]],'C13*') or WildMatch([@[Statuses]],'C20*') or WildMatch([@[Statuses]],'C21*') or WildMatch([@[Statuses]],'C22*'), 'Reportable ENT', 'Non-Reportable')))))


Or you can add haiku(*) before of the search string as well

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable
Author

Hi Thanks for the reply. I have using first code of you provided.. after that i am facing

error1.PNG

Not applicable
Author

Hi nagaraju, I have using first code as your provided. when ever loading that i am facing below error

IF(isnum(SubStringCount([Statuses],'P3')) OR (isnum(SubStringCount([Statuses],'C6')),'Succerror12.PNG

i.e., IF(isnum(SubStringCount([Statuses],'P3')) OR (isnum(SubStringCount([Statuses],'C6')),'Success',

Anil_Babu_Samineni

Is there any chance to share application with sample data

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable
Author

Hi,

Can you please tell me how can i share my application.

There is no option in this discussion