Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
We value your feedback – take our 5-minute QlikView modernization survey
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Countif

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

16 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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
Author

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
Partner - Specialist
Partner - Specialist

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
Partner - Specialist
Partner - Specialist

QV_Community.JPG

Not applicable
Author

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
Author

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
Author

Thank you very much Sathish

It is working