Find value in the column and derive the result

[#1]

I have a table , which contains the following data.

tcode	LicenseTypeID
CJ20N	1
CJ20N	2
ME2N	1
ME2N	2
ME23N	1
ME23N	2
ME51N	1
ME51N	2

I want an expresssion, which will lookup for each unique tcode , if the licensetypeid '2' is available for that tocde and return text "All available in type 2'

[tresesco]

try :

Concat ( Distinct (LicenseTypeID) , ' ; ')

[#3]

Hi Tres

The above code always gives 1;2

[tresesco]

My mistake, try:

Concat ( Distinct (tcode) , ' ; ')

[#5]

Thanks Tres

Your code gives me the unique tcodes, what i want is to find if all the tcodes available in the col A has licensetypeid='2' or not?

[tresesco]

this ?

=If(count(DISTINCT(tcode))= count(distinct {<LicenseTypeID={2}>} DISTINCT tcode), 'All available in type 2'', 'NO')

[#7]

Thanks Tres , I did more or less the same and it worked

= if((count( DISTINCT tcode))= ( Count( DISTINCT if( LicenseTypeID='2' or IsNull( LicenseTypeID ),tcode))),'LP','P')

Thanks

[#8]

Hi Tres

How would i achieve this through scripting? lets say my data is

Userid	tcode	LicenseTypeID
abc	CJ20N	1
abc	CJ20N	2
abc	ME2N	1
abc	ME2N	2
xyz	ME23N	1
xyz	ME23N	2
xyz	ME51N	1
xyz	ME51N	2
xyz	SU01	1

The result should check each tcode for each user, and if the tcodes for individual user lies in License type id='2' , it should suggest "Userid is of licensetypeid 2" and if the user's any one tcode does not have licensetypeid 2, it should suggest "Userid is of licensetypeid 1"

UserID	Suggestion
abc	2
xyz	1

Kind Regards

[daveamz]

Hi,

I don't think this is the best approach but see the file attached.

Best regards,

David