topic Find the correct value in QlikView

Find the correct value

Anonymous — Wed, 11 Dec 2013 13:18:26 GMT

Hi,

I have a table with the following main fields (among others); [Task], [Change date], [Delivery deadline], [Progress %]

The table contents could look like this:

1, 2013-12-01, 2013-12-30, 0

1, 2013-12-05, 2013-12-28, 50

2, 2013-12-01, 2013-12-13, 0

2, 2013-12-02, 2013-12-12, 0

2, 2013-12-10, 2013-12-15, 50

2, 2013-12-12, 2013-12-13, 100

2, 2013-12-15, 2013-12-13, 100

My problem is to write an expression to get the correct Delivery deadline for the latest Change date (and that for each Task).

I only have 10 Tasks so I will make a separate expression for each one.

So for Task 1 it would be 2013-12-28 and for Task 2: 2013-12-13

I have already solved what I thought was a harder expression; to find the first change date when Progress is 100, but that expression won't help me with this one...

Re: Find the correct value

hic — Wed, 11 Dec 2013 14:15:59 GMT

Data:

Load * inline

[Task, Change date, Delivery deadline, Progress %

1, 2013-12-01, 2013-12-30, 0

1, 2013-12-05, 2013-12-28, 50

2, 2013-12-01, 2013-12-13, 0

2, 2013-12-02, 2013-12-12, 0

2, 2013-12-10, 2013-12-15, 50

2, 2013-12-12, 2013-12-13, 100

2, 2013-12-15, 2013-12-13, 100];

Inner Join

Load

Task,

Max([Change date]) as [Change date]

Resident Data

Group By Task;

/HIC

Re: Find the correct value

Anonymous — Wed, 11 Dec 2013 14:48:08 GMT

Try with a link table with only the task number and the max update date

see exemple

Re: Find the correct value

Anonymous — Wed, 11 Dec 2013 15:01:50 GMT

Tack Henric,

Would probably work for me (I ran it and it works of course), but I would rather not do it in the load section, but instead have an expression that find the value(s) for me.

// Peter

Re: Find the correct value

hic — Wed, 11 Dec 2013 16:16:15 GMT

Then you should make a chart with Task as dimension and

FirstSortedValue([Delivery deadline],-[Change date])

as expression.

HIC

Re: Find the correct value

Anonymous — Thu, 12 Dec 2013 14:49:13 GMT

Henric,

I did as you first suggested and solved it as an inner join in the load section.

But now I found out that my clever expression to find the first Change date with the Progress =100 didn't work in all cases. How would you suggest a solution to find this?

In task 1: there would be none found but in task 2: it would return 2013-12-12.

Found the solution... Just made a new load statement with a "Where Progress=100" and that solved it.

So now I have three tables: The original with a lot of columns, a table with the Max change date and a table with the Min change date when the progress is 100%.

So thanks for the help.

// Peter