topic Re: Complex logic in QlikView

Complex logic

smilingjohn — Wed, 25 Nov 2020 16:16:04 GMT

Hi Expertise,

PLease find the attachment of excel file and the qvw application.

IN the data i have , ID, Status and score.

I have two types of ID's one is TS and the other is OS ... to calculate the score i need to show as ID's only when the TS and OS should be either have status as Accepted or Completed.

If one is ID is having status as completed and other is in different status or its not present than we should not show it ..

Say for example for ID =18.1TS status is completed and for 18.1OS the status is Idea then i should not show 18.1 score in my table.

Similarly for 18.7 only TS is present OS is not there therefore i should not show it ..

I Need to show only when TS and OS for ID's should be present in Completed or Accepted else i should not show it ..

I have grouped 18.1 TS and 18.OS as 18.1 and so on..

Need your help in achieving this ..

Thanks in Advance

Re: Complex logic

sunny_talwar — Thu, 23 Aug 2018 11:34:26 GMT

Try this

Sum({<ID_NAME = {"=Count(Status) = 2 and Match(Concat(DISTINCT Status, ','), 'Completed', 'Accepted', 'Accepted,Completed')"}>}Score)

Re: Complex logic

smilingjohn — Thu, 23 Aug 2018 12:30:50 GMT

Hi Sunny,

Thanks this is working ...

Can you please explain me how this expression works .It will be really a great help

Thanks a lot once again

Re: Complex logic

sunny_talwar — Thu, 23 Aug 2018 12:47:24 GMT

Sure,

Sum({<ID_NAME = {"=Count(Status) = 2 and Match(Concat(DISTINCT Status, ','), 'Completed', 'Accepted', 'Accepted,Completed')"}>}Score)

Count(Status) is checking if ID_NAME has 2 Statuses or not.....

Match(Concat(DISTINCT Status, ','), 'Completed', 'Accepted', 'Accepted,Completed') is checking if the Statuses are either 2 Completed or 2 Accepted or Completed and Accepted

If the above 2 conditions meet, then Sum the Score of the ID_NAME

Re: Complex logic

smilingjohn — Thu, 23 Aug 2018 13:17:25 GMT

Thanks a lot sir,

You are just awesome