Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

MVP
MVP

Re: Countif

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

    )

)

  

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Countif

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

Partner
Partner

Re: Countif

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

Highlighted
Partner
Partner

Re: Countif

QV_Community.JPG

Not applicable

Re: Countif

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

Not applicable

Re: Countif

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

Not applicable

Re: Countif

Thank you very much Sathish

It is working