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

[Saravanan_Desingh]

Ok, I was not clearly explained to you. The Inline Loads will be replaced with QVDs like below.

Tasks_Table:
LOAD Task_Number
From Your_Task_Qvd.Qvd (qvd);

Subtask_Table:
LOAD Task_Number, Subtask_Number, SubTask_Status
From Your_SubTask_Qvd.Qvd (qvd);

It doesn't matter if a Task has SubTask or not. Please try to run with your live data and share the results.

[sekrish]

still a bit unclear on this. So are you saying all of the below should be in the expression editor for the table dimension?

Tasks_Table:
LOAD Task_Number
From Your_Task_Qvd.Qvd (qvd);

Subtask_Table:
LOAD Task_Number, Subtask_Number, SubTask_Status
From Your_SubTask_Qvd.Qvd (qvd);

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

the last part (If statement) is valid for the expression but the rest is not.. also the qvd files are already loaded in the load script editor. why load them again?

[vamsee]

It's two different solutions we are talking about here.

Either create a column while you're loading your QVD as suggested by me or Saran 

Your script will look like

Tasks_Table:
LOAD Task_Number
From Your_Task_Qvd.Qvd (qvd);

Subtask_Table:
LOAD Task_Number, Subtask_Number, SubTask_Status,If(Status='Done', 1, 2) as Status_num
From Your_SubTask_Qvd.Qvd (qvd);
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]
;

and use Task_Number as Dimension, your expression

If(Good_Flag=1, 'Good', 'Pending')

or

If(Good_Flag=1, 'Y', 'N')

OR

Try this if you want to use a calculated dimension

=If(Aggr(Rank(SubTask_Status),Task_Number)=1, 'Y', 'N')

[vamsee]

My suggestion would be to create the flag at Script level as Calculated Dimensions would be re-calculated every time the user clicks. Response times will be faster if the field is calculated in the script.

[sekrish]

Hi Vamsee, thanks for the clarification. the front end approach does seem to be causing a lot of reloading.

Could you help me understand your code for the script? what is the '1','2' etc...? My Subtask_status is purely alphabetic - 'Done' , 'In progess' etc... another issue is that my subtasks table is really json data received from the REST connector, so its really 3 tables joined together. subtask_num, and subtask_status are seperate tables (joined by FK). what is the subtask ID? is it different from the subtask_number or was that just an oversight? what is the purpose of the CHECK, GOOD_FLAG and the left join etc...

[sekrish]

AS a workaround, I am thinking of displaying just a count of the number of subtasks that are not in 'Done' or '' state. Even that is giving me issues

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

 appreciate the assistance

[vamsee]

If you have millions of records, then using calculated dimensions would increase Calc Time on your charts. In simple words, the charts would take longer to show data.  

I just defined Good as 1 and 2 as In Progress, as I prefer my flags to be numbers (When they are not bein displayed to the end users). If you feel uncomfortable with renaming, you can use Saran's approach too. 

The data source is not an issue here, as its already been stored in a qvd. I didn't rename the field names from my initial post, 

Task ID is your Task_Number.

According to my understanding, for each task number you have multiple sub-tasks, out of which one, none or all can have status as done. 

When you want a chart to display all the task numbers which are completely done, all the sub-tasks under the task number have to be done, i.e. the data has to be checked at Task Number level not at Sub Task level. To achieve this solution, we are grouping by Task_Number.

As explained earlier, the script level approach creates a flag "Good_Flag", which can be used in expressions. And, when set to 1 would display only the good Task_numbers.

You can use this Flag in multiple charts, instead of creating calculated dimensions in every one of them.

[vamsee]

To Tasks in Done state

Subtask_Status={'Done'}

To Tasks which are not Done

Subtask_Status-={'Done'}

[sekrish]

sorry to be taking this in a different direction but 

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

gives me an 'error  in expression'. also to account for tasks without a subtask (status), should this be something like 

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

? 

[vamsee]

Try

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

It can be 

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