Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

ecabanas
Contributor

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

Re: Problem with date in loading script

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

Re: Problem with date in loading script

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;

hirishv7
Honored Contributor

Re: Problem with date in loading script

Hi,

Try like this,

where created_at >='42369'


or


Where Floor(created_at) >='42369'

-Hirish

Re: Problem with date in loading script

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

Re: Problem with date in loading script

Redundant discussion, solved here: Error loading data

Re: Problem with date in loading script

Floor is not supported in Mysql.?

Re: Problem with date in loading script

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

Re: Problem with date in loading script

Both Floor and Ceiling functions are supported in MySql.

Re: Problem with date in loading script

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

Community Browser