Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
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.

Labels (3)
14 Replies
mlarruda
Creator
Creator
Author

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

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

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

Worked! Thanks.