Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Highlighted
Contributor

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

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:

NAMETASK1TASK2
JohnAAABBB
JohnCCC 
MaryAAA 
AnnaBBBDDD
AnnaEEEFFF


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.

1 Solution

Accepted Solutions
Highlighted

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

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

14 Replies
Highlighted
Partner
Partner

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

Try wildmatch:

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

 

Highlighted
Contributor

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

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

Highlighted
Partner
Partner

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

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

 

Highlighted
Contributor

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

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.

Highlighted
Partner
Partner

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

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

clipboard_image_0.png

Refer qvw attached as reference.

Thanks and regards,

Arthur Fong

 

Highlighted
Contributor

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

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

Highlighted
Partner
Partner

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

Can you attach your sample qvw?

Highlighted
Contributor

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

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.

Highlighted

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

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