Countif

[#1]

Hi,

I have the following If formula in QlikView:

"

If(If(If(Count({<[Prematch Counterparty Long Name]=P([PM Automatch Depot])>}[Prematch Counterparty Long Name])>0,'Y','N')='Y','Matched',

  If(Count({<[Prematch Stock Instruction Status]=P([PM Matched])>}[Prematch Stock Instruction Status])>0,'Matched',

  If(Left([Prematch Instrument Settlement],2)='AU' or Left([Prematch Instrument Settlement],2)='NZ' or Left([Prematch Instrument Settlement],4)='CHBD' ,'Matched',

  If(Left([Prematch Diary Narrative Orig],7)='MATCHED' ,'Matched',

  If(([Prematch Instrument Settlement Orig]='GBEQ BOTH' OR [Prematch Instrument Settlement Orig]='IEEQ BOTH')

  AND COUNT([Prematch Stock Instruction Status])>0 AND [Prematch Report_Date]>=[Prematch Report_Value Date],'Matched','Unmatched')))))

  ='Matched'

"

I want to count all the Matched from the formula above. Please can you assist because it doesn't work.

Also how would I be able to create a Set Analysis formula for the above?

Thanks,

Mary

[sathishkumar_go]

could you post your application here?

--Sathish

[#3]

Hi Sathish,

Thank you for the prompt reply .

What do you mean by application ?

Thanks,

Mary

[harsh44_bhatia]

Hi mary.pham,

i would suggest using mixmatch() and pick() functions to formulate your expression.

read about them in the help file and give it a try.

[sathishkumar_go]

I have told to attach your QlikView application.

-Sathish

[#6]

Hi Sathish,

Please find attached the application. QlikView is reading from multiple Text files.

The aim is to use the If formula below as an expression, to count the number of Matched by Depot.

(see table 1 "1_Breakdown of Matched/Unmatched"):

"If(If(Count({<[Prematch Counterparty Long Name]=P([PM Automatch Depot])>}[Prematch Counterparty Long Name])>0,'Y','N')='Y','Matched',

If(Count({<[Prematch Stock Instruction Status]=P([PM Matched])>}[Prematch Stock Instruction Status])>0,'Matched',

If(Left([Prematch Instrument Settlement],2)='AU' or Left([Prematch Instrument Settlement],2)='NZ' or Left([Prematch Instrument Settlement],4)='CHBD' ,'Matched',

  If(Left([Prematch Diary Narrative Orig],7)='MATCHED' ,'Matched',

  If(([Prematch Instrument Settlement Orig]='GBEQ BOTH' OR [Prematch Instrument Settlement Orig]='IEEQ BOTH')

  AND COUNT([Prematch Stock Instruction Status])>0 AND [Prematch Report_Date]>=[Prematch Report_Value Date],'Matched','Unmatched')))))"

When I try and use this in another table to show the count of Matched in a pivot, the formula does not work and I receive no results.

Thank you very much for your help

Mary

[#7]

Hi Harshit,

I have tried and it does not work.

Thanks for your advice.

Mary

[sathishkumar_go]

Hi Mary,

Its little complex formula.

I will suggest, calculate the Matched or unmatched in the script itself.

-Sathish

[#9]

Thanks Sathish

How can I calculate the Matched or Unmatched in the script itself because some of the fields are from different tables so we would have to join the text files - and this doesn't work either .

Thanks,

Mary

[jonathandienst]

Your expression is syntactic nonsense, that's why it does not work, but I think this may be what you are looking for:

If(  
    Count({<[Prematch Counterparty Long Name]=P([PM Automatch Depot])>} [Prematch Counterparty Long Name]) > 0
        Or
    Count({<[Prematch Stock Instruction Status]=P([PM Matched])>} [Prematch Stock Instruction Status]) > 0
        Or
    WildMatch([Prematch Instrument Settlement], 'AU*', 'NZ*', 'CHBD*')
        Or
    WildMatch([Prematch Diary Narrative Orig], 'MATCHED*')
        Or
    (
        Match([Prematch Instrument Settlement Orig], 'GBEQ BOTH', 'IEEQ BOTH')
        And
        Count([Prematch Stock Instruction Status]) > 0 
        And 
        [Prematch Report_Date] >= [Prematch Report_Value Date]
    )
, 'Matched'
, 'Unmatched'
)