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
Floor() is a qv function which is probably not supported (in the same way) in mysql and you might need to to specify the condition slightly different - maybe like: where year(created_at)>=2016;
Otherwise you will find here more about such conditions on mysql:
https://www.google.de/search?q=mysql+where+date
- Marcus
Hi
Try like this
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 Year(created_at)>2015;
or
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
where floor(created_at)>42369; //or Year(created_at)>2015
SQL SELECT created_at ,order_id, product_id ,price, gift,quantity_in_order,quantity_purchased,quantity_delivered,code FROM ulaoffice.NS_order_lines;
Hi,
Try like this,
where created_at >='42369'
or
Where Floor(created_at) >='42369'
-Hirish
may be
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
where floor(created_at)>42369;
SQL SELECT created_at ,order_id, product_id ,price, gift,quantity_in_order,quantity_purchased,quantity_delivered,code FROM ulaoffice.NS_order_lines
Redundant discussion, solved here: Error loading data
Floor is not supported in Mysql.?
I think rounding-functions will be implemented within the most sql-databases but I'm not sure if they are called equally - literally and logically (parameter are optional or not).
- Marcus
Both Floor and Ceiling functions are supported in MySql.
Everyone seems to agree that DATETIME values are stored in every RDBMS in exactly the same way as in Excel (from which QV took its cue)?
What does the floor(timestamp)-call do in an RDBMS context?
Peter