Help with Set Analyssis - comparing two fields

[shane_spencer]

If have two fields

PARTY_SECONDARY_TAX_COUNTRY_src_1

PARTY_SECONDARY_TAXNUMBER_clo_1

The Country is 2 letters, i.e. GB.

The Tax Number is 2 letters and then some numbers i.e. IE12344345

I want to do a count of the number of records where the Country is the same as the first two letters of the Tax Number. This will go in a text box not a chart so no dimensions.

I've tried set analysis but not sure where I've gone wrong as it's not working:

=Count({< PARTY_SECONDARY_TAX_COUNTRY_src_1= {"=Left(PARTY_SECONDARY_TAXNUMBER_clo_1,2)"} >}PARTY_SECONDARY_TAX_COUNTRY_src_1)

[m_woolf]

Maybe:

=Count({< PARTY_SECONDARY_TAX_COUNTRY_src_1= {"$(=Left(PARTY_SECONDARY_TAXNUMBER_clo_1,2))"} >}PARTY_SECONDARY_TAX_COUNTRY_src_1)

[shane_spencer]

Doesn't seem to work. 

[marcus_sommer]

You could try this one:

=Count({< PARTY_SECONDARY_TAX_COUNTRY_src_1= {"=PARTY_SECONDARY_TAX_COUNTRY_src_1=Left(PARTY_SECONDARY_TAXNUMBER_clo_1,2)"} >}PARTY_SECONDARY_TAX_COUNTRY_src_1)

- Marcus

[Anil_Babu_Samineni]

How about this?

=Count(If(PARTY_SECONDARY_TAX_COUNTRY_src_1=Left(PARTY_SECONDARY_TAXNUMBER_clo_1,2), PARTY_SECONDARY_TAX_COUNTRY_src_1))

[shane_spencer]

Marcus, I'm not sure what that's doing but I got a count of 2 which isn't right. Maybe it's doing a count distinct. 😕

[shane_spencer]

Didn't quite work but I modified it to the following which I think may be working.... (the tricky bit is that I've got a fair bit of set analysis in there already so it made sense to use set analysis rather than IF.

I've attached the QVW if anyone has got any better ideas to test.

=Count( {< TAX_CODE_LOCATION_clo_1={'EU'}, TAX_CODE_FLOW_clo_1={'I'}, TAX_CODE_EU_INDICATOR_clo_1-={'Non-EC relevant'}, TAX_CODE_STATUS_clo_1-={'SELF CHARGE'} >} If(PARTY_SECONDARY_TAX_COUNTRY_src_1=Left(PARTY_SECONDARY_TAXNUMBER_clo_1,2),1,null()) )

[marcus_sommer]

It depends on the datamodel if an approach like above mentioned worked or not. Beside this are you sure how your data does look like, maybe there are really two records available - quite useful for checking it is to add recno() or rowno() for an unique record-id within the script and then using a tablebox to check the data.

- Marcus

[marcus_sommer]

Usually it's better to use: if(condition, count(Field)) instead of: count(if(condition, Field)) - just give it a try.

- Marcus