Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I agree that it wil not work same in Qlikview as like Mysql.
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?
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).