topic Re: Displaying Days Delayed in QlikView

Displaying Days Delayed

Tue, 26 Feb 2013 05:12:05 GMT

Hi Guys,

I need help regarding my table. (PSA)

I have to put an input box which will filter the days delayed.

ex:

If I type 2, it will filter all the dates that have 2days delayed.

Any ideas?

Thanks,

Donna

Re: Displaying Days Delayed

sujeetsingh — Tue, 26 Feb 2013 05:53:50 GMT

Donatzz,

Go for a set analysis and use calculated dimension as

if(inputvariable>0,only({$<Delay=$(inputvariable)>} id)>,id)

something like this,

Re: Displaying Days Delayed

Tue, 26 Feb 2013 23:43:23 GMT

please see attached qvd. I don't have a field for Days Delayed and it is only computed.

Re: Displaying Days Delayed

stevedark — Wed, 27 Feb 2013 00:21:48 GMT

Hi there,

Whilst it would be possible to write an expression that works on the computed value for delay it would be more robust to have that value in the data model. In order to achieve this I would suggest a join in your SQL statement onto a grouped sub-query.

You would have something like:

LOAD
id,

date,

firstdate,

floor(date - firstdate) as delay

;

SQL SELECT

o.id,

o.date,

f.firstdate

FROM orders o

LEFT OUTER JOIN

(

SELECT

id,

min(date) as firstdate

FROM orders

GROUP BY id

) f

ON f.id = o.id

With the hard work done in the load script you could just have a simple selection on the number of days delayed, or could use set analysis on a variable if you wished.

Hope I have understood your query correctly and this is of help.

- Steve

http://www.quickintelligence.co.uk/

Re: Displaying Days Delayed

Wed, 27 Feb 2013 01:07:48 GMT

Thank you for the reply. Sadly in my script I only have one date field which makes it harder. Thank you

Re: Displaying Days Delayed

stevedark — Wed, 27 Feb 2013 07:09:51 GMT

Hi there,

In the example I gave the field 'firstdate' doesn't exist in the source database, it is derived at the point the load script is run from the other date field.

The line that does this is:

min(date) as firstdate

In the QVW you uploaded there was a SQL connection, so I presumed you would be able to construct a similar SQL statement in your load.

If your data is not in a SQL database then you can do similar using a resident load and a JOIN or ApplyMap.

Hope that helps,

Steve

http://www.quickintelligence.co.uk/

Re: Displaying Days Delayed

Wed, 27 Feb 2013 08:10:47 GMT

thanks, is using min(date) considers what specific id the record have?

I don't know how it works, or it will check the min date of all records. if that happens, it won't work since the 1st date and the last date to be subracted should be depending on a specific id.

Re: Displaying Days Delayed

stevedark — Wed, 27 Feb 2013 11:38:04 GMT

Hi Donna,

That firstdate field will be derived for each id in the sub query, because

of the GROUP BY in the SQL statement. This is a standard SQL function - so

I would recommend Googling it and reading up on the syntax.

- Steve

http://www.quickintelligence.co.uk/