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:
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.
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;
Thank you very very much. It worked but I still have two doubts
First: what if I have a task "AAAA" which IS NOT in the special tasks set? Will the condition '*AAA*' find this task? If yes, how can I avoid it and assure that only "AAA" will be find?
Second: is it possible to define somewhere something like
('AAA','BBB','CCC','XXX','ZZZ') as SPECIAL_TASKS
so I can just refer to it (rather than type all the list of special tasks) in the wildMatch function:
IF(wildMATCH(aggr(concat(distinct TASKS, ' / '),NAME), SPECIAL_TASKS), 'Y', 'N') ?
Try adding an extra condition to read only 3 characters:
IF(wildMATCH(aggr(concat(distinct TASKS, ' / '),NAME), '*AAA*','*BBB*','*XXX*','*ZZZ*')and Len(NAME)=3, 'Y', 'N').
Thank you, but "AAA", "BBB", "CCC" etc. are clearly ficticious task names. Their real names have several lenghts and so this solution to read only N characters doesn't work.
Thank you, but I already have a Tab1, built by a CrossTable statement.
So, to avoid rerun a long script, I created a new tab, typed it:
if(match(Value,'AAA','BBB','XXX','ZZZ'),'Y','N') AS Special_Task
and ran the partial reload.
And it didn't worked! No Special_Task variable was created...
Sure. I'm attaching two samples. Test1 is exactly what you did, while in Test2 I first ran the main tab and then added the "Special Tasks" tab and ran a partial reload.