Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

populate a field based on multiple other fields

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

Labels (1)
1 Solution

Accepted Solutions
Highlighted
Specialist
Specialist

Re: populate a field based on multiple other fields

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)

 

View solution in original post

28 Replies
Highlighted
Specialist
Specialist

Re: populate a field based on multiple other fields

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}

Highlighted
Contributor II
Contributor II

Re: populate a field based on multiple other fields

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_NumberTask_Status
Task1N
Task2Y
Task3N


Subtask_Table

Task_NumberSubtask_NumberSubTask_Status
Task1Subtask1Done
Task1Subtask2Done
Task1Subtask3In Progress
Task2Subtask1Done
Task2Subtask2Done
Task3Subtask1In Progress
Highlighted
Specialist III
Specialist III

Re: populate a field based on multiple other fields

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;
Highlighted
Specialist III
Specialist III

Re: populate a field based on multiple other fields

Output.

commQV86.PNG

Highlighted
Specialist III
Specialist III

Re: populate a field based on multiple other fields

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
];
Highlighted
Specialist III
Specialist III

Re: populate a field based on multiple other fields

Dimension: Task_Number

Expression:

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

Output.

commQV87.PNG

 

Highlighted
Contributor II
Contributor II

Re: populate a field based on multiple other fields

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?

Highlighted
Specialist III
Specialist III

Re: populate a field based on multiple other fields

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')
Highlighted
Contributor II
Contributor II

Re: populate a field based on multiple other fields

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