topic Re: populate a field based on multiple other fields in QlikView

populate a field based on multiple other fields

sekrish — Sat, 16 Nov 2024 00:24:54 GMT

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

Re: populate a field based on multiple other fields

vamsee — Thu, 25 Jun 2020 21:06:08 GMT

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}

Re: populate a field based on multiple other fields

sekrish — Fri, 26 Jun 2020 20:16:47 GMT

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

Re: populate a field based on multiple other fields

Saravanan_Desingh — Fri, 26 Jun 2020 23:50:53 GMT

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;

Re: populate a field based on multiple other fields

Saravanan_Desingh — Fri, 26 Jun 2020 23:51:34 GMT

Output.

Re: populate a field based on multiple other fields

Saravanan_Desingh — Fri, 26 Jun 2020 23:54:36 GMT

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 ];

Re: populate a field based on multiple other fields

Saravanan_Desingh — Fri, 26 Jun 2020 23:56:18 GMT

Dimension: Task_Number

Expression:

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

Output.

Re: populate a field based on multiple other fields

sekrish — Sat, 27 Jun 2020 18:29:23 GMT

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?

Re: populate a field based on multiple other fields

Saravanan_Desingh — Sat, 27 Jun 2020 19:26:05 GMT

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')

Re: populate a field based on multiple other fields

sekrish — Sat, 27 Jun 2020 22:28:24 GMT

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

Re: populate a field based on multiple other fields

Saravanan_Desingh — Sun, 28 Jun 2020 01:05:35 GMT

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.

Re: populate a field based on multiple other fields

sekrish — Mon, 29 Jun 2020 14:52:54 GMT

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?

Re: populate a field based on multiple other fields

vamsee — Mon, 29 Jun 2020 15:28:08 GMT

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')

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

Try this if you want to use a calculated dimension

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

Re: populate a field based on multiple other fields

vamsee — Mon, 29 Jun 2020 15:30:38 GMT

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.

Re: populate a field based on multiple other fields

sekrish — Mon, 29 Jun 2020 17:19:35 GMT

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

Re: populate a field based on multiple other fields

sekrish — Mon, 29 Jun 2020 20:15:11 GMT

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

Re: populate a field based on multiple other fields

vamsee — Mon, 29 Jun 2020 20:45:39 GMT

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.

Re: populate a field based on multiple other fields

vamsee — Mon, 29 Jun 2020 20:50:22 GMT

To Tasks in Done state

Subtask_Status={'Done'}

To Tasks which are not Done

Subtask_Status-={'Done'}

Re: populate a field based on multiple other fields

sekrish — Mon, 29 Jun 2020 22:00:18 GMT

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-={''}>)

Re: populate a field based on multiple other fields

vamsee — Mon, 29 Jun 2020 22:21:27 GMT

Try

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

It can be

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