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

[vamsee]

If you have two tables and joined by task ID, try

Load * Inline [
Task ID,	Task
1,	ABC
2,	DEF
3,  XYZ
]
;
B:		
Load
	*,
	If(Status='Done', 1, 2) as Status_num
;	
LOAD * INLINE [
    Task ID, Sub Task, Status
    1, 1, Done
    1, 2, In Progress
    1, 3, Done
    2, 1, Done
    2, 2, Done
    2, 3, Done
    3, 1, Done
    3, 2, Done
    3, 3, In Progress
    3, 4, In Progress
];

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

In your expression use Good_Flag={1}

[sekrish]

this seems to be on the load script editor side. can we do this in the chart table, by saying,
'if status of all subtasks  in this row with this task id is done then task_status='Y' else 'N' ' ?

how would i translate this to set analysis syntax in the chart?

say assuming tables as below (as previously mentioned)

Tasks_Table

Task_Number	Task_Status
Task1	N
Task2	Y
Task3	N

Subtask_Table

Task_Number	Subtask_Number	SubTask_Status
Task1	Subtask1	Done
Task1	Subtask2	Done
Task1	Subtask3	In Progress
Task2	Subtask1	Done
Task2	Subtask2	Done
Task3	Subtask1	In Progress

[Saravanan_Desingh]

One solution in Script.

Tasks_Table:
LOAD * INLINE [
    Task_Number
    Task1
    Task2
    Task3
];

Subtask_Table:
LOAD * INLINE [
    Task_Number, Subtask_Number, SubTask_Status
    Task1, Subtask1, Done
    Task1, Subtask2, Done
    Task1, Subtask3, In Progress
    Task2, Subtask1, Done
    Task2, Subtask2, Done
    Task3, Subtask1, In Progress
];

tab1:
LOAD Task_Number, If(Concat(DISTINCT SubTask_Status)='Done', 'Y', 'N') As Task_Status
Resident Subtask_Table
Group By Task_Number
;

Drop Table Tasks_Table, Subtask_Table;

[Saravanan_Desingh]

Output.

[Saravanan_Desingh]

Front end solution.

Script:

Subtask_Table:
LOAD * INLINE [
    Task_Number, Subtask_Number, SubTask_Status
    Task1, Subtask1, Done
    Task1, Subtask2, Done
    Task1, Subtask3, In Progress
    Task2, Subtask1, Done
    Task2, Subtask2, Done
    Task3, Subtask1, In Progress
];

[Saravanan_Desingh]

Dimension: Task_Number

Expression:

If(Concat(DISTINCT SubTask_Status)='Done', 'Y', 'N')

Output.

[sekrish]

Hi, a bit of a newbie to qlik. Do you mean i should create the inline table for task and subtasks in the script editor for the front end solution before the 

If(Concat(DISTINCT SubTask_Status)='Done', 'Y', 'N')

?

there aIready exist task and subtask tables, both containing the task_number field, being loaded by the load script editor.  I tried just the above expression as a dimension, but while the expression is valid, i get invalid dimension. Could it be because the subtask_status can be null in some cases?

[Saravanan_Desingh]

Yes, you have run the Inline Script to get the data.

Then the Dimension is Task_Number.

Use the below Expression.

If(Concat(DISTINCT SubTask_Status)='Done', 'Y', 'N')

[sekrish]

but the task table and subtask table are currently read from a qvd in the load script editor and the contents are changed periodically and contain 1000's of rows. how would i create the new table inline in the chart script editor in this case...also some of the tasks dont contain subtasks...