topic Re: Why adding a column in my table makes all go wrong ? in QlikView

Why adding a column in my table makes all go wrong ?

Mon, 09 Mar 2015 17:15:24 GMT

Hello everyone,

I have a table like this :

Event_id; timestamp_beginning ; timestamp_end ; delay_event (calculated field in minutes)

1 16/10/2014 19:42:00 17/10/2014 18:02:00 1340

There is one row for each event_id.

In my QV application, I have created a text zone where I have entered sum(delay_event) and I do get a result which looks ok.

I wanted to do a graph per month/week with a master calendar. (A calendar which contains 24 rows per day since a date I have specified).

But when I add Floor( timestamp_beginning, 1/24) as Date in my table in order to link with the master calendar, the result of "sum(delay) goes wrong... Even if I pout the calendar in comment (and so my table is not linked to another), I still get a wrong result.

What can explain that ??

Thank you for your help

Have a good day.

Re: Why adding a column in my table makes all go wrong ?

Gysbert_Wassenaar — Mon, 09 Mar 2015 18:13:06 GMT

If your calendar table contains dates and your other table contains timestamps then only the timestamps with 00:00:00 as time part will be linked to the calendar table.Splitting the timestamp into a date and a time field should help:

LOAD ...other fields...,

date(floor(MyTimeStamp),'DD-MM-YYYY') as MyDate,

fract(MyTimeStamp) as MyTime

FROM ....

Change the date format MM-DD-YYYY to whatever format you prefer.

Re: Why adding a column in my table makes all go wrong ?

Fri, 13 Mar 2015 14:35:53 GMT

Thank you for your reply Gysbert.

Sadly, the problem also occurs when my table is not linked to the calendar table (I have changed the name of the date field so it doesn't match any of the fields of the calendar table).

So if I sum up, when I do CTRL+T, I can see the table is "alone", linked to no tables.

If I do a table in the application with the fields:

Event_id; timestamp_beginning ; timestamp_end ; delay_event (calculated field in minutes)

1 16/10/2014 19:12:00 16/10/2014 19:42:00 29

then I make a textbox with the formula : =sum(delay_event)

Then if I reload :

TheTable:

LOAD Event_id,

timestamp_beginning ,

//Floor( timestamp_beginning , 1/24) as Date2,

timestamp_end ,

delay_event

FROM

If I filter on event_id =1, I do get 29 in the textbox, but if I remove the comment for Date2, in the textbox, I have 0,0207...

Really don't get why because my table is not linked to any other

Thank you

Re: Why adding a column in my table makes all go wrong ?

Gysbert_Wassenaar — Fri, 13 Mar 2015 14:44:47 GMT

Can you post a qlikview document that demonstrates the problem?

Re: Why adding a column in my table makes all go wrong ?

Fri, 13 Mar 2015 15:04:22 GMT

Hi Gysbert,

Here it is. So yoou can see the table with the value : 29 and in the textbox, I have entered sum(...) and I get 0,02...

Thank you for your help

Re: Why adding a column in my table makes all go wrong ?

Gysbert_Wassenaar — Fri, 13 Mar 2015 17:27:16 GMT

Charge_durée_branchement is probably a time interval value formatted as minutes. The numeric value is 0,02... You can check this by changing the Number format in the listbox. If you want the sum expression to be displayed in minutes then you'll have to change the number format to Interval. In your text box you can change the expression to: = 'Sum = ' & interval(sum(Charge_durée_branchement),'m')

Re: Why adding a column in my table makes all go wrong ?

Mon, 16 Mar 2015 08:38:54 GMT

Hi Gysbert,

Thank you, you are right. Charge_durée_branchement was calculated as Interval(stopped_at -started_at ,'mm').

I understand ! It's just aboout formating. The 0,02 is in "days" ( i mean the unit of date in qlikview), so when I do 0,02*24*60, I do get 29,8 minutes.

Thank you very much !

Have a good day

Laura

Re: Why adding a column in my table makes all go wrong ?

Gysbert_Wassenaar — Mon, 16 Mar 2015 08:41:43 GMT

The interval function merely formats a number as a time interval. The first parameter is a number, the second parameter is the format string. You can find more information in the help file.