populate a field based on multiple other fields

[sekrish]

Hi

I have a table with tasks and subtasks

task1     subtask1     subtask1_status
task1     subtask2     subtask2_status

task2     subtask1     subtask1_status
task2     subtask2     subtask2_status

task2     subtask3     subtask1_status
task3    subtask1     subtask1_status

task3    subtask2     subtask2_status

I would like to populate a new field task_status that will say 'done' if all the subtask_status are done or 'In progress' if even one subtask is in progress. I am guessing this will need to be done using set analysis, but how would i go about it?
also need to know how this would work if tasks and subtasks are in different tables (joined by a task_number)

thanks

[sekrish]

Error in expression : count takes 1 parameter

[vamsee · Accepted solution]

My bad I forgot you are a newbie. You have to mention the column name you are counting.

Try

Count({$<Subtask_Status-={'Done'}>}  Field_Name)

It can be 

Count({$<Subtask_Status-={'Done', ' '}>} Field_Name)

[sekrish]

sorry about that. it works now but is only showing the fields which are non zero.  I would rather it show the fields that have 0 subtasks (Done or no subtasks) as well. I have made this a measure, so there is no option to "show nulls". I get invalid dimension when i try to use this as a dimesnion...

[vamsee]

Handling Nulls is a completely different concept.

Refer Null Handling in Qlik

If you want to show all tasks remove the Set Analysis part

Count(Field_Name)

You cannot use this as a dimension directly, it can be used with Rank() as I mentioned in my previous response

[sekrish]

So I guess the set analysis approach wont work either for this case. going back to the previous load script approach  with the FLAG etc..., I get Table B not found. Would you know what might be causing it

The following error occurred:
Table 'B' not found
The error occurred here:
Left Join(B)
Load 
		[request_num],
		1 as Good_Flag
Where 
Check=1​

[vamsee]

That's just the syntax! You have to replace with the actual table names

Left Join(Subtask_Table)
Load 
		Task_Number,
		1 as Good_Flag
Where 
Check=1​;
Load
		Task_Number,
		Max(Status_num) as Check
Resident Subtask_Table
Group by [Task ID]
;

[sekrish]

ah ok, i thought you were creating some kind of temp table. Thanks

[sekrish]

Given that the the  subtask_number and subtask_status are in different tables (with a FK relationship), I am getting an error 'subtask_num' not found when i attempt the left join on the subtask_status table. tried left join on the subtask_main  and then a left join on subtask_Status table, but that didnt help as well. table structure (as received from the REST connector) is as below:

[Subtask_fields_table]:
LOAD
	[summary],
	[task_num],
	[assignee],
	[__KEY_fields] AS [-_KEY_fields],
	[__FK_fields] AS [-_KEY_issues];
SQL SELECT 
	(SELECT 
		(SELECT 
			"summary",
			"task_num",
			"assignee",
			"__KEY_fields",
			"__FK_fields"
		FROM "fields" PK "__KEY_fields" FK "__FK_fields")
	FROM "issues" PK "__KEY_issues" FK "__FK_issues")
FROM JSON (wrap on) "root" PK "__KEY_root" QDL;

[subtask_status_table]:
LOAD
	[name] AS [subtask_Status],
	[__KEY_status],
    If(name='Done', 1, 2) as [Status_num],
	[__FK_status] AS [-_KEY_fields];
SQL SELECT 
	(SELECT 
		(SELECT 
			(SELECT 
				"name",
				"__KEY_status",
				"__FK_status"
			FROM "status" PK "__KEY_status" FK "__FK_status")
		FROM "fields" PK "__KEY_fields" FK "__FK_fields")
	FROM "issues" PK "__KEY_issues" FK "__FK_issues")
FROM JSON (wrap on) "root" PK "__KEY_root" QDL;

[sekrish]

never mind, I reduced the redundant data being loaded considerably and the front end solution you mentioned works fine now. thanks for the help and I hope to learn the tool better as we go along 🙂 only been using qlik sense for a week or so now.

[Mastan]

Hi Vamsee

These are all of the audits that have selected “No” for Data Usage (Yes/No).  All of the data field names are highlighted in blue.  For the Quality Control sheet, I want to see all audits where there is a data field highlighted in yellow.  Meaning they said No they did not use data on the audit but answered the data questions as if they did use data on their audit.

Am attaching Excel file