topic Re: Help with Set Analyssis - comparing two fields in QlikView

Help with Set Analyssis - comparing two fields

shane_spencer — Sat, 16 Nov 2024 04:42:34 GMT

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)

Re: Help with Set Analyssis - comparing two fields

m_woolf — Mon, 21 Jan 2019 15:42:32 GMT

Maybe:

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

Re: Help with Set Analyssis - comparing two fields

shane_spencer — Mon, 21 Jan 2019 15:44:22 GMT

Doesn't seem to work.

Re: Help with Set Analyssis - comparing two fields

marcus_sommer — Mon, 21 Jan 2019 15:54:37 GMT

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

Re: Help with Set Analyssis - comparing two fields

Anil_Babu_Samineni — Mon, 21 Jan 2019 15:57:28 GMT

How about this?

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

Re: Help with Set Analyssis - comparing two fields

shane_spencer — Tue, 22 Jan 2019 09:48:20 GMT

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. 😕

Re: Help with Set Analyssis - comparing two fields

shane_spencer — Tue, 22 Jan 2019 09:50:48 GMT

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()) )

Re: Help with Set Analyssis - comparing two fields

marcus_sommer — Tue, 22 Jan 2019 10:29:05 GMT

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

Re: Help with Set Analyssis - comparing two fields

marcus_sommer — Tue, 22 Jan 2019 11:13:22 GMT

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

- Marcus