topic Re: Countif in QlikView

Countif

Mon, 08 Feb 2016 14:18:08 GMT

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

Re: Countif

sathishkumar_go — Mon, 08 Feb 2016 16:39:06 GMT

could you post your application here?

--Sathish

Re: Countif

Mon, 08 Feb 2016 16:41:24 GMT

Hi Sathish,

Thank you for the prompt reply .

What do you mean by application ?

Thanks,

Mary

Re: Countif

harsh44_bhatia — Mon, 08 Feb 2016 16:46:39 GMT

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.

Re: Countif

sathishkumar_go — Mon, 08 Feb 2016 16:52:14 GMT

I have told to attach your QlikView application.

-Sathish

Re: Countif

Tue, 09 Feb 2016 10:15:27 GMT

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

Re: Countif

Tue, 09 Feb 2016 10:19:33 GMT

Hi Harshit,

I have tried and it does not work.

Thanks for your advice.

Mary

Re: Countif

sathishkumar_go — Tue, 09 Feb 2016 11:22:31 GMT

Hi Mary,

Its little complex formula.

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

-Sathish

Re: Countif

Tue, 09 Feb 2016 11:29:55 GMT

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

Re: Countif

jonathandienst — Tue, 09 Feb 2016 11:45:34 GMT

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

Re: Countif

jonathandienst — Tue, 09 Feb 2016 11:50:13 GMT

If you need to count the number of items that get matched in the expression above, you could try:


Sum(                                                  
    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]
        )
    ,1
    )
)

Re: Countif

Tue, 09 Feb 2016 12:03:07 GMT

Thank you Jonathan for your suggestion.

The second formula does not work when I enter it into the second table (in the attached)- only returning null values

Re: Countif

sathishkumar_go — Tue, 09 Feb 2016 12:47:43 GMT

Hi Mary,

I have done in the load script.

1) copy your application and save as different qvw name

2) use binary load

Binary ;

3) copy the below script

Map_PM_Automatch_Depot:
Mapping
LOAD [PM Automatch Depot],
1 as Flag
resident [PM_ Automatch Depot];

Map_PM_Matched:
mapping
LOAD [PM Matched],
1 as Flag
resident PM_Matched;

Count_Prematch_Stock_Instruction_Status:
load
ORDER as ORDER_Count,
count([Prematch Stock Instruction Status]) as [Prematch Stock Instruction Status Count Flag]
Resident Prematch Group by ORDER;

Map_Prematch_Stock_Instruction_Status:
Mapping
load
ORDER_Count as ORDER_Map,
1 as Flag
Resident Count_Prematch_Stock_Instruction_Status where if([Prematch Stock Instruction Status Count Flag] > 0,1,0) = 1 ;

[Prematch_Temp]:
load
*,

IF(APPLYMAP('Map_PM_Automatch_Depot',[Prematch Counterparty Long Name],0) = 1,'Matched',
if(APPLYMAP('Map_PM_Matched',[Prematch Stock Instruction Status],0) = 1,'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 (APPLYMAP('Map_Prematch_Stock_Instruction_Status',ORDER,0) = 1 AND [Prematch Report_Date]>=[Prematch Report_Value Date]),'Matched','Unmatched'))))) as Status_Match

Resident [Prematch];

drop table [Prematch];

exit script;

4) use this expression

count(if(Status_Match = 'Matched',Status_Match))

-Sathish

Re: Countif

sathishkumar_go — Tue, 09 Feb 2016 12:55:31 GMT

Re: Countif

Tue, 09 Feb 2016 13:27:00 GMT

Hi Sathish,

I have created a copy of the application as GE Daily MI v3.qvw.

Then Clicked on Edit Script and updated the Binary code to reference back to the original, then copied the code you attached. However I am getting an error

Unknown statement

Binary"

So I tried clicking on "Data from Files" -> "QlikView File" -> GE Daily MI v2.qvw

Binary [ge daily mi v2.qvw];

However I also get another error

Please assist.

Thank you very much for your help with this

Re: Countif

Tue, 09 Feb 2016 13:41:18 GMT

Hi Sathish,

Please ignore the last message, I have included the Binary code as the first row and it is loading now.

I will test and get back to you

Thanks again.

Mary

Re: Countif

Tue, 09 Feb 2016 14:09:46 GMT

Thank you very much Sathish

It is working