How to remove time stamp from a field?

[#1]

Hi all,

I have a field that contains date and time in a DD/MM/YYYY HH:MM:SS format; I do not need time for my analysis and in fact, the time stamp is making it difficult to create MinDate and MaxDate for Master Calendar, based on the above field (I'm new to QlikView btw so perhaps I should approach the Master Calendar differently, please advise if that's the case).

Is there a way to eliminate the time value from my field, or perhaps to limit the load of this field to first 10 characters?

Many thanks

[PrashantSangle]

Hi,

Try this,

Date(Date#(fieldname,'DD/MM/YYYY HH:MM:SS'),'DD/MM/YYYY') as fieldname

or

Date(TimeStamp#(fieldname,'DD/MM/YYYY HH:MM:SS'),'DD/MM/YYYY') as fieldname

Regards,

[MK_QSL · Accepted solution]

=DATE(FLOOR(Timestamp#(YourTimeStampField)),'DD/MM/YYYY')

if you want to create a calendar use.

=MIN(NUM(DATE(FLOOR(Timestamp#(YourTimeStampField)),'DD/MM/YYYY')))

and

=MAX(NUM(DATE(FLOOR(Timestamp#(YourTimeStampField)),'DD/MM/YYYY')))

[MK_QSL]

Below script will tell you how to create Master Calendar from Min and Max TimeStamp.

DateField:

Load * Inline

[

  DateField

  19/03/2014 11:31:14

  31/03/2014 20:12:12

];

MinMax:

Load

  MIN(NUM(DATE(FLOOR(Timestamp#(DateField)),'DD/MM/YYYY'))) as MinDate,

  MAX(NUM(DATE(FLOOR(Timestamp#(DateField)),'DD/MM/YYYY'))) as MaxDate

Resident DateField;

Let vMin = NUM(Peek('MinDate',0,'MinMax'));

Let vMax = NUM(Peek('MaxDate',0,'MinMax'));

===============================================

Now you can create

Month, Year, WeekDay, Day, MonthYear, Quarter etc...

Drop Table MinMax;

Calendar:

LOAD

  date ($(vMin) + rowno() -1) as Date

AUTOGENERATE

$(vMax)- $(vMin)+1;

[ToniKautto]

The first thing is to confirm if the loaded data format matches your format variables in QlikView. If the a loaded value for example is '25/06/2010 11:22:53' and your application has the timestamp format 'DD/MM/YYYY HH:MM:SS', then QlikView will automatically interpret the value as a timestamp.

If this is the case then the stored value would be a timestamp, meaning that it has an underlying numerical value that is a decimal value. In the decimal value the integer part represents the date and the decimal part represents the time. In order to convert such value to a date you need to first eliminate the decimal part and then format the value to a date.

Date(Floor(TimeStamp#(Value)))

If the loaded value does not match your timestamp format then the value will be loaded as text. In this case you can create the date value directly by using the Date functions. In this case you must specify the incoming time stamp format, so that the Date#() function knows how to parse the loaded text value.

Date(Floor(TimeStamp#(Value, 'DD/MM/YYYY hh:mm:ss')))

There is never a need to convert a Date or Timestamp value to a number in order to use it in aggregations or for example find the largest or smallest value. The simple reason for this is that a properly formatted Date or Timestamp value always has an underlying numerical value. The important part is to make sure that the underlying value for a Date does not have a decimal part and that a Time value does not have a integer part, while a Timestamp value is expected to have an underlying decimal value as it's numerical value.

To find out how QlikView interpreted the incoming value, you can use the Num() function to reveal the underlying numerical value,

[ToniKautto]

Max() and Min() functions return a numerical value, so the Num() should not be required as a intermediate format. Since the max an min are numerical values already during load, there is no need to use Num() when you create the variables.

[#7]

Hi Toni,

Thank you for explaining it in detail, however I don't seem to be able to apply it to my code, please have a look at my test code and point me in the right direction.

Many thanks

[ToniKautto]

Sorry for the late reply Katarzyna Cwik. Please see the attached example as a suggestion for changes and with some observations.

The DateTime table probably should have a defined key field, which is formatted as Date. This will allow for your calendar table to properly link to the data. It is commonly good to indicate which field are key field, so that you more easily can avoid using them in expressions or dimensions. The HidePrefix system variable increases the invisibility of the key field, by not showing them in the field lists inside the application interface.

In the MinMax table I would recommend removing the format variable as second parameter in the Date() function. There is also no need to format the value as a TimeStamp, since the values are already properly formatted in the DateTime table.

With the data properly formatted as Dates in the MinMax table, there is no longer a need for additional format when you generate the variable values. The variable will now be Date formatted in the same way as the Peeked table MinMax.

In the TempCal table, I would recommend using $(#variable) expansion to expand the actual numerical value from the variables dual value. For a date value this means that you will not expand the visual date like 17/06/2012, instead the expansion will expand the numerical value 41077. When you are interested in using the actual numerical value of a variable, always use the $(#variable) expansion and you will never have to consider the actual visual format of the variable.

Always keep in mind that the dollar expansion is done before the script line is executed, which means that you can evaluate the dollar expansion result in the script reload log;

QlikView desktop client > Settings > Document Properties > General > Generate Logfile

Finally in the Master Calendar table the key field needs to be altered to match the key created in the DateTime table. Notice that the key field %Date is also stored in identical field called Date. The reason for this is to allow calculations of usage of the data in the key field. Always avoid calculations on a key field, since this actually refers to two tables and the outcome of the data can be a bit unpredicable.

Hope this helped you progress with your project.

[Eduard23]

Hi Can you help me for this one, need to remove the time stamp and the AM, i try the solution you gave its working if there will be no AM

=DATE(FLOOR(Timestamp#(YourTimeStampField)),'DD/MM/YYYY')

Expiry_Date	Expected Value
31/3/24 12:00:00 AM	2024/03/31