topic Re: Implement Excel Formula in QlikView in QlikView

Implement Excel Formula in QlikView

Thu, 06 Apr 2017 13:05:47 GMT

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

So kindly help me..

Thanks in advance..

Note: Edited by Community Moderator for clarity

Re: Implement Excel Formula in QlikView

vishsaggi — Thu, 06 Apr 2017 15:26:56 GMT

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

Re: Implement Excel Formula in QlikView

Peter_Cammaert — Thu, 06 Apr 2017 15:27:21 GMT

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

Re: Implement Excel Formula in QlikView

Fri, 07 Apr 2017 04:44:37 GMT

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

Re: Implement Excel Formula in QlikView

Anil_Babu_Samineni — Fri, 07 Apr 2017 05:07:45 GMT

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.

Re: Implement Excel Formula in QlikView

Anil_Babu_Samineni — Fri, 07 Apr 2017 05:20:36 GMT

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

Re: Implement Excel Formula in QlikView

Fri, 07 Apr 2017 06:11:27 GMT

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

Re: Implement Excel Formula in QlikView

Fri, 07 Apr 2017 07:31:43 GMT

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')),'Succ

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

Re: Implement Excel Formula in QlikView

Anil_Babu_Samineni — Fri, 07 Apr 2017 07:55:30 GMT

Is there any chance to share application with sample data

Re: Implement Excel Formula in QlikView

Mon, 10 Apr 2017 02:36:32 GMT

Hi,

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

There is no option in this discussion

Re: Implement Excel Formula in QlikView

Anil_Babu_Samineni — Mon, 10 Apr 2017 03:33:11 GMT

Here you go

Uploading a Sample

Re: Implement Excel Formula in QlikView

Peter_Cammaert — Mon, 10 Apr 2017 07:57:24 GMT

You can still edit your original question in this discussion by selecting the Edit item in the Actions menu in the top right corner. Then follow the instructions in Anil's linked article.

You don't need to create a new thread as this will be confusing to posters and no real question is being asked in the second thread. Please remove it from the forum and post your attachments in your original quesiton at the top. Thanks.

Re: Implement Excel Formula in QlikView

Tue, 11 Apr 2017 02:44:39 GMT

Hi How can i remove forum..

Can you guide me

Re: Implement Excel Formula in QlikView

Anil_Babu_Samineni — Tue, 11 Apr 2017 04:52:37 GMT

Here you go for Peter Suggestion

Re: Implement Excel Formula in QlikView

Tue, 11 Apr 2017 06:46:53 GMT

Hi Anil

I need to implement below formula also

can you please help me

=IF([@[Source Type]=114,"EBO",IF([@[Source Type]]=115,"Listed",IF(OR([@[Source Type]]=105, [@[Source Type]]=32,[@[Source Type]]=45, [@[Source Type]]=142,[@[Source Type]]=147, [@[Source Type]]=87,[@[Source Type]]=162), "FI",IF(OR([@[Source Type]]=132, [@[Source Type]]=32,[@[Source Type]]=27, [@[Source Type]]=182,[@[Source Type]]=157), "Active",IF(OR([@[Source Type]]= 28,[@[Source Type]]=72,[@[Source Type]]=97, [@[Source Type]]=172,[@[Source Type]]=72, [@[Source Type]]=167),"Passive",IF(OR([@[Source Type]]=0, [@[Source Type]]=1,[@[Source Type]]=2), “HV”,[@[part Type]]))))))

Re: Implement Excel Formula in QlikView

Anil_Babu_Samineni — Tue, 11 Apr 2017 06:55:42 GMT

This is straight forward, In Qlik you can use this

IF([@[Source Type]=114,"EBO",

IF([@[Source Type]]=115,"Listed",

IF([@[Source Type]]=105 or [@[Source Type]]=32 or [@[Source Type]]=45 or [@[Source Type]]=142 or [@[Source Type]]=147 or [@[Source Type]]=87 or [@[Source Type]]=162, "FI",

IF([@[Source Type]]=132 or [@[Source Type]]=32 or [@[Source Type]]=27 or [@[Source Type]]=182 or [@[Source Type]]=157), "Active",

IF([@[Source Type]]= 28 or [@[Source Type]]=72 or [@[Source Type]]=97 or [@[Source Type]]=172 or [@[Source Type]]=72 or [@[Source Type]]=167),"Passive",

IF([@[Source Type]]=0 or [@[Source Type]]=1 or [@[Source Type]]=2, “HV”,[@[part Type]]))))))

FYI ...

This will work as like image. In Qlik it work as If(Color = 'Green' or Color = 'Red', 'TRUE', 'FALSE')

Re: Implement Excel Formula in QlikView

Tue, 11 Apr 2017 08:04:34 GMT

Hi,

Thanks for reply.

I am using this code. After that i getting the following error

in that above code we have given a close braces on 152) and 167) . when ever reload the script i getting above scrren shot error.

Re: Implement Excel Formula in QlikView

Peter_Cammaert — Tue, 11 Apr 2017 08:18:18 GMT

In the Actions menu underneath "Edit" and "Move", there is a "Delete" button. Press that one.

Maybe you should take some time to do a quick read of Qlik Community Tip: How to Delete a Post

And fo a long-running discussion like this: Qlik Community Tip: Marking Replies as Correct or Helpful

Re: Implement Excel Formula in QlikView

Anil_Babu_Samineni — Tue, 11 Apr 2017 09:29:05 GMT

Seems, issue here. check Red text and then remove that it will work

IF([@[Source Type]=114,"EBO",

IF([@[Source Type]]=115,"Listed",

IF([@[Source Type]]=105 or [@[Source Type]]=32 or [@[Source Type]]=45 or [@[Source Type]]=142 or [@[Source Type]]=147 or [@[Source Type]]=87 or [@[Source Type]]=162, "FI",

IF([@[Source Type]]=132 or [@[Source Type]]=32 or [@[Source Type]]=27 or [@[Source Type]]=182 or [@[Source Type]]=157), "Active",

IF([@[Source Type]]= 28 or [@[Source Type]]=72 or [@[Source Type]]=97 or [@[Source Type]]=172 or [@[Source Type]]=72 or [@[Source Type]]=167),"Passive",

IF([@[Source Type]]=0 or [@[Source Type]]=1 or [@[Source Type]]=2, “HV”,[@[part Type]]))))))

Re: Implement Excel Formula in QlikView

Peter_Cammaert — Tue, 11 Apr 2017 12:26:47 GMT

In the first line, there is one right square bracket missing in the field name as well...

IF([@[Source Type]]=114,"EBO",