Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
)
)
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
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
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
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
Thank you very much Sathish
It is working