Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
mlarruda
Creator
Creator

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
sunny_talwar

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
Arthur_Fong
Partner
Partner

Try wildmatch:

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

 

mlarruda
Creator
Creator
Author

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

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
Creator
Creator
Author

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

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

clipboard_image_0.png

Refer qvw attached as reference.

Thanks and regards,

Arthur Fong

 

mlarruda
Creator
Creator
Author

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

Can you attach your sample qvw?

mlarruda
Creator
Creator
Author

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

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