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
marcus_sommer

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

MayilVahanan

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;

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
HirisH_V7
Master
Master

Hi,

Try like this,

where created_at >='42369'


or


Where Floor(created_at) >='42369'

-Hirish

HirisH
“Aspire to Inspire before we Expire!”
Kushal_Chawda

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Redundant discussion, solved here: Error loading data

tamilarasu
Champion
Champion

Floor is not supported in Mysql.?

marcus_sommer

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

tamilarasu
Champion
Champion

Both Floor and Ceiling functions are supported in MySql.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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