Solved: Re: Checking if a set of values contains values fr... - Qlik Community

mlarruda · ‎2019-09-24

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.

sunny_talwar · ‎2019-09-30

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;

View solution in original post

Arthur_Fong · ‎2019-09-24

Try wildmatch:

IF(wildMATCH(aggr(concat(distinct TASKS, ' / '),NAME), '*AAA*','*BBB*','*XXX*','*ZZZ*'), 'Y', 'N').

mlarruda · ‎2019-09-25

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') ?

Arthur_Fong · ‎2019-09-25

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').

mlarruda · ‎2019-09-25

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.

Arthur_Fong · ‎2019-09-25

Try to build this at script level using crosstable():

Refer qvw attached as reference.

Thanks and regards,

Arthur Fong

mlarruda · ‎2019-09-27

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:

Task:
load *,
if(match(Value,'AAA','BBB','XXX','ZZZ'),'Y','N') AS Special_Task
resident Tab1;

and ran the partial reload.

And it didn't worked! No Special_Task variable was created...

Arthur_Fong · ‎2019-09-27

Can you attach your sample qvw?

mlarruda · ‎2019-09-30

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.

sunny_talwar · ‎2019-09-30

Why are you doing partial reload and not the complete reload here?

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

Aggr or Set Analysis

Match multiple values

sets