Compare 2 fields and aggregate of the field in Set analysis

[shmkmr90]

I want to compare the INSTANCE_ID and substring(FILE_NAME) in the set analysis and aggregating the INSTANCE_ID. I tried in two ways, but seems not working. Can we use the field used to aggregate be used in the condition of fields which we equate?

Error is error in set modifier element function name:} expected

Could someone help me.

=count({<INTERFACE_ID={101},FILE_NAME ={"*|*"},INSTANCE_ID=left(FILE_NAME,index(FILE_NAME,'|',1)-1)>}INSTANCE_ID)

=count({<INTERFACE_ID={101},FILE_NAME ={"*|*"},INSTANCE_ID=P(left(FILE_NAME,index(FILE_NAME,'|',1)-1))>}INSTANCE_ID)

Many Thanks for the help.

Regards,

Shyam

[Clever_Anjos]

You can´t do this because Set Analysis is not evaluated for each INSTANCE_ID.

A simple if() can do that you need

count({<INTERFACE_ID={101},FILE_NAME ={"*|*"}>} if(INSTANCE_ID=left(FILE_NAME,index(FILE_NAME,'|',1)-1),INSTANCE_ID))

[sunny_talwar]

May be try this

=Count({<INTERFACE_ID={101}, FILE_NAME ={"*|*"}, INSTANCE_ID = {"$(=Left(FILE_NAME, Index(FILE_NAME, '|', 1) - 1))"}>} INSTANCE_ID)

or

=Count({<INTERFACE_ID={101}, FILE_NAME ={"*|*"}, INSTANCE_ID = {"=INSTANCE_ID = Left(FILE_NAME, Index(FILE_NAME, '|', 1) - 1)"}>} INSTANCE_ID)

[Kushal_Chawda · Accepted solution]

why not to create the flag in script to simplify further

LOAD *

        if(INSTANCE_ID=left(FILE_NAME,index(FILE_NAME,'|',1)-1),1) as Flag

.....

FROM QVD

Then on front end

count({<INTERFACE_ID={101},FILE_NAME ={"*|*"},Flag={1}>}INSTANCE_ID))

[sunny_talwar]

why not to create the flag

Kyunki yeh tera idea hai

[Kushal_Chawda]

yes, if possible flag hi use karna chahiye, tera kya khayal he?

[sunny_talwar]

baat toh ek dum lakh takke ki karta hai bhai Jokes apart, I agree with you