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

[jonathandienst]

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

[#3]

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

[sathishkumar_go · Accepted solution]

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

[sathishkumar_go]

[#6]

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

[#7]

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

[#8]

Thank you very much Sathish

It is working