topic Re: Logic help in QlikView

Logic help

blunckc1 — Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone,

I have the following data model.

Table 1 - Objectives

Objective_Title

Table 2 - Tasks

Task_Title

Task_Status

Objective_Title

Table 1 contains a unique Objective_Title in each row of data.

Table 2 will contain the Objective_Title multiple times as there are numerous tasks for each.

Table 1 and 2 are obviously linked through the Objective_Title.

My problem is that I need to be able to identify the current status of the Objective_Title based on the Task_Status.

Here is some script to load:

LOAD * INLINE [

Objective_Title

Qlik Development

SharePoint Development

Qlik Training

Sharepoint Training

];

Tasks:

LOAD * INLINE [

Task_Title, Task_Status, Objective_Title

Get data, Complete, Qlik Development

Build data model, In progress, Qlik Development

Pilot with end user, Not yet applicable, Qlik Development

Business case for SharePoint, Complete, Sharepoint Development

Build site, Complete, Sharepoint Development

Test site, Complete, Sharepoint Development

Launch site, Complete, Sharepoint Development

Identify end users, Complete, Qlik Training

Book training rooms, In progress, Qlik Training

Run training, Not yet applicable, Qlik Training

];

I want to create a new table in the load script bthat would have:

Objective_Title

Objective_Status

The results of this table would be:

Objective_Title	Objective_Status	Reason/Commentary
Qlik Development	In progress	Not ALL tasks linked to this objective has a Task_Status of "Complete"
Qlik Training	In progress	Not ALL tasks linked to this objective has a Task_Status of "Complete"
SharePointDevelopment	Complete	ALL tasks linked to this objective has a Task_Status of "Complete"
SharePoint Training	Unassigned	There are no tasks with linked to this objective

This way I could create a pie chart where by the Objective_Status is the Dimension and can use the expression of Count(Objective_Title).

Any suggestions?

Thanks

Carl

Message was edited by: Carl Blunck

Re: Logic help

rahulpawarb — Tue, 31 Jan 2017 07:18:40 GMT

Hello Carl,

Thank you for sharing your concern. It would handy if you could help us by providing some sample data with expected output. This will help us to share our thoughts on this.

Regards!

Rahul

Re: Logic help

sdmech81 — Tue, 31 Jan 2017 07:23:53 GMT

Do left join btn 1and 2and table.

Then write the same if statement and store in one attribute using the resident load from above join result table.

Use join don't apply map else it ll consider only latest row for task status.

Sachin

Re: Logic help

vinieme12 — Tue, 31 Jan 2017 08:03:13 GMT

Assign numeric value to [TaskStatus] field during load and then use Maxstring(TaskStatus)

Load

Dual(Taskstatus,pick(Match(TaskStatus,'Unassigned','inProgress','Completed'),0,1,2)) as TaskStatus

Re: Logic help

blunckc1 — Tue, 31 Jan 2017 23:38:54 GMT

Hi all,

Here is some script to load:

LOAD * INLINE [

Objective_Title

Qlik Development

SharePoint Development

Qlik Training

Sharepoint Training

];

Tasks:

LOAD * INLINE [

Task_Title, Task_Status, Objective_Title

Get data, Complete, Qlik Development

Build data model, In progress, Qlik Development

Pilot with end user, Not yet applicable, Qlik Development

Business case for SharePoint, Complete, Sharepoint Development

Build site, Complete, Sharepoint Development

Test site, Complete, Sharepoint Development

Launch site, Complete, Sharepoint Development

Identify end users, Complete, Qlik Training

Book training rooms, In progress, Qlik Training

Run training, Not yet applicable, Qlik Training

];

Expected result would be a new table in the load script being created that would have:

Objective_Title

Objective_Status

The results of this table would be:

Objective_Title	Objective_Status	Reason
Qlik Development	In progress	Not ALL tasks linked to this objective has a Task_Status of "Complete"
Qlik Training	In progress	Not ALL tasks linked to this objective has a Task_Status of "Complete"
SharePointDevelopment	Complete	ALL tasks linked to this objective has a Task_Status of "Complete"
SharePoint Training	Unassigned	There are no tasks with linked to this objective

This way I could create a pie chart where by the Objective_Status is the Dimension and can use the expression of Count(Objective_Title).

Cheers

Carl

Re: Logic help

blunckc1 — Tue, 31 Jan 2017 23:39:08 GMT

See below

Re: Logic help

Anil_Babu_Samineni — Tue, 31 Jan 2017 23:43:00 GMT

Where is this Reason coming from?

Re: Logic help

blunckc1 — Tue, 31 Jan 2017 23:44:33 GMT

Reason is just commentary to show why that result should be produced.

Not sure how to achieve that through script logic, hence the thread

Re: Logic help

Anil_Babu_Samineni — Wed, 01 Feb 2017 04:17:16 GMT

Not clear to me well. What is the logic behind to do this? Don't look script try from Front end if so will catch up in script too. Would you describe more