Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ecabanas
Creator II
Creator II

Problem with date in loading script

Hi,

I did this script to load data from MySQL and I have a problem. The table has records from part of 2015 and 2016, but I only want to load the 2016 records.

The field created_at is a timestamp and did not do the filter that I desire:

SalesLine:

LOAD created_at as CreatedatSalesLine,

order_id,

'PV' & right('00000000'&order_id,8) as SalesId,

if(IsNull(product_id),code,product_id)as ItemId,

//code as ItemId,

price,

gift,

quantity_in_order,

quantity_purchased,

quantity_delivered,

1+(ApplyMap('Tax_Item_Map',product_id)/100)as Tax,

(price/1+(ApplyMap('Tax_Item_Map',product_id)/100))as Import_Brut,

ApplyMap('Date_Invoice_Map',order_id)as Date_Invoice;

SQL SELECT created_at ,order_id, product_id ,price, gift,quantity_in_order,quantity_purchased,quantity_delivered,code FROM ulaoffice.NS_order_lines

where floor(created_at)>42369;

Many thank's for your help

Eduard

12 Replies
tamilarasu
Champion
Champion

I agree that it wil not work same in Qlikview as like Mysql.


Peter_Cammaert
Partner - Champion III
Partner - Champion III

Well the floor() function will operate the same in SQL as in QV. But an RDBMS will most probably store a Timestamp differently from QlikView, QlikView uses Excel binary storage in which the integer part represents the number of days since xx/12/1899 and the fraction represents the time as part of a day (0.5 meaning 12:00)

But does an (every) RDBMS store DATETIME values in the same way? And how about DATE values?

tamilarasu
Champion
Champion

Thanks for the explantion. No. DATE and DATETIME storing procedure (format) differs based on the database. (In mysql and MSSql, default format is YYYY-MM-DD whereas DD-MON-YY in Oracle).