Checking if a set of values contains values from other set.

[mlarruda]

Well, this is a bit complex question, so let me introduce it.

Suppose i have a table with three columns NAME, TASK1 and TASK2. The same person (i.e. the same NAME) can appear in more than one row. So, we have something like it:

NAME	TASK1	TASK2
John	AAA	BBB
John	CCC
Mary	AAA
Anna	BBB	DDD
Anna	EEE	FFF

and so.

My goal is to analyze the set of tasks of each person. So, firstly, in the initial script I set:

SubField(TASK1&'///'&TASKS2,'///') as TASKS,

And, later, in my tables, I use the dimension

aggr(concat(distinct TASKS, ' / '),NAME).

Now, I have a set of "special tasks" and my goal is find the persons (NAMEs) which have at least one "special task" in his/her set of tasks.

I tried this dimension

IF(MATCH(aggr(concat(distinct TASKS, ' / '),NAME), 'AAA'), 'Y', 'N')

but it didn't work. Also, I don't know how I could replace the match condition 'AAA' by a set like ('AAA', 'BBB', 'XXX', 'ZZZ').

Anyone could help me, please?

Many thanks in advance.

[mlarruda]

Because in my real file, I have a very long main tab, where many files are loaded. So, I prefer to don't do a full reload, which would last more than one hour to be finished.

[sunny_talwar]

Try this

tempTask:
CrossTable(TASK,Value,1)
LOAD * INLINE [
    NAME, TASK1, TASKS2
    John, AAA, BBB
    John, CCC
    Mary, AAA
    Anna, BBB, DDD
    Anna, EEE, FFF
];

If IsPartialReload() then

	Task:
	Add LOAD *,
		 If(Match(Value,'AAA','BBB','XXX','ZZZ'),'Y','N') AS [Special Task]
	Resident tempTask;
	
	DROP Table tempTask;

ENDIF;

[mlarruda]

Thanks. It worked well. But, due to the statement DROP Table tempTask, when I edited the Special Tasks list and redid the partial reload, it failed, because tempTask table no longer existed.

[sunny_talwar · Accepted solution]

If you plan on doing multiple partial reloads, you can do this

tempTask:
CrossTable(TASK,Value,1)
LOAD * INLINE [
    NAME, TASK1, TASKS2
    John, AAA, BBB
    John, CCC
    Mary, AAA
    Anna, BBB, DDD
    Anna, EEE, FFF
];

If IsPartialReload() then

	IF FieldValueCount('Special Task') > 0 THEN
		
		DROP Field [Special Task];

	ENDIF;
	
	Task:
	Add LOAD *,
		 If(Match(Value,'AAA','BBB','XXX','ZZZ'),'Y','N') AS [Special Task]
	Resident tempTask;
	
	DROP Table tempTask;
	RENAME Table Task to tempTask;

ENDIF;

[mlarruda]

Worked! Thanks.