topic Time fields in QlikView

Time fields

Thu, 01 Sep 2011 12:48:50 GMT

Hi,

I'm a qlikview enduser and I have a question regarding a time dimension and more precisely a delay time

I must calculate a time field but what's the best solution?

I create a script or a calculated dimension in a pivot table? And how

I have 2 fields actualdate and actduedate

For instance, if actduedate>actualdate then delay but how must I create this?

Thanks

Best regards

Re: Time fields

its_anandrjs — Thu, 01 Sep 2011 12:55:20 GMT

Hi,

You need to create calculated espression for both dates you need to use some thing like this in your in code

1. If you create this in load script you need to load a resident table of your table like

Load

If ( actduedate > actualdate , 'Delay' , 'Ontime') as Status

Resident YourTable;

2. If you want to create calculated dimension so like this

If ( actduedate > actualdate ), 'Delay' , 'Ontime') you need to put some code like this and you get the

But best is in Load script

Regards

Anand

Time fields

Thu, 01 Sep 2011 13:05:27 GMT

re,

But my 2 fields are in 2 different tables (actduedate in Act and actualdate in Lig)

How I can do?

Thanks

Re: Time fields

its_anandrjs — Thu, 01 Sep 2011 13:11:43 GMT

Hi,

Ok you need to create a pivot table on that when you put the fields actualdate and actduedate in next to both the field type a calculated code for status like

if(actduedate>actduedate1,'Delay','Ontime') and named it what ever you like

or also you check if there is relation ship between two tables or not.

Regards,

Anand

Time fields

Thu, 01 Sep 2011 13:17:21 GMT

re,

Excuse me I understand but with delay I'm just an enduser

I try with your code

Load

If ( actduedate > actualdate ), 'Delay' , 'Ontime') as Status

Resident YourTable;

but I can't run

Can you help me?

Re: Time fields

its_anandrjs — Thu, 01 Sep 2011 13:35:31 GMT

Hi,

Suppose there is two different table but there is common file AccNo so reload it and see the code

TableA:

LOAD Date(actduedate) as actduedate,

AccNo

FROM

..\Data.xlsx

(ooxml, embedded labels, table is Sheet23);

TableB:

LOAD Date(actualdate) as actualdate,

Value,

AccNo

FROM

..\Data.xlsx

(ooxml, embedded labels, table is Sheet25);

And after of these when you create a pivot type this code

if(actduedate>actualdate,'Delay','Ontime') for status

See the attached sample

Regards,

Anand

Time fields

Thu, 01 Sep 2011 13:57:07 GMT

Hi,

I try your solution but I can't run the code.

I have forgotten to give an important detail sometimes I can't calculate because of a lack date.

Example

Script

//ACTSQL SELECT ACTACTION,
    ACTDUEDATE,
    Day(ACTDUEDATE) as Duedate,
    ACTLIBELLE,
    ACTNOTIFICATIONDATE,
    EVIDENCE,
    RESPONSIBLE
FROM ACT;

//LIGNESQL SELECT ACTUALDATE,
       Day (ACTUALDATE) as Aldate,
    LIGNE,
    NA,
    NO,
    NUMERO,
    QUESTION as question,
    STATUS,
    YES
FROM LIGNE;

=if(Duedate>Aldate,'Delay','Ontime')

Impossible to visualize a result:

Problemwith datas:

Time fields

its_anandrjs — Thu, 01 Sep 2011 14:01:42 GMT

Hi,

Is this your solution or not that i suggest you and let me know what is the status for the application.

Regards,

Anand

Time fields

Thu, 01 Sep 2011 14:12:30 GMT

Hi,

Yes, it looks like to my solution but How do I handle empty cells? and tell impossible to calculate or not to display result?

Thanks a lot

Time fields

its_anandrjs — Thu, 01 Sep 2011 14:19:28 GMT

Hi,

Thanks, Yes it is possible to handle empty cells that are called null cells for handle that just select that field in dimension tab and select Suppress When Value Is Null option check marked if it is not work there is any other field so select same option for both.To remove null values from any field it is best way.

Hope this helps you.

Regards

Anand

Time fields

Thu, 01 Sep 2011 15:00:26 GMT

Hi,

Sorry but I don't understand because I select "suppress if null value" in Dimension and "suppress if null value" in Presentation and Qlikview doesn't handle cells empty!!

Where is the problem for it?

Thanks a lot