topic Re: Problem with date in loading script in QlikView

Problem with date in loading script

ecabanas — Tue, 05 Jan 2016 07:50:52 GMT

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

Re: Problem with date in loading script

marcus_sommer — Tue, 05 Jan 2016 12:07:11 GMT

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

MayilVahanan — Tue, 05 Jan 2016 12:11:58 GMT

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;

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;

Re: Problem with date in loading script

HirisH_V7 — Tue, 05 Jan 2016 12:18:58 GMT

Hi,

Try like this,

where created_at >='42369'

Where Floor(created_at) >='42369'

-Hirish

Re: Problem with date in loading script

Kushal_Chawda — Tue, 05 Jan 2016 12:21:24 GMT

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

Peter_Cammaert — Tue, 05 Jan 2016 12:23:55 GMT

Redundant discussion, solved here: Error loading data

Re: Problem with date in loading script

tamilarasu — Tue, 05 Jan 2016 12:39:48 GMT

Floor is not supported in Mysql.?

Re: Problem with date in loading script

marcus_sommer — Tue, 05 Jan 2016 12:46:48 GMT

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

tamilarasu — Tue, 05 Jan 2016 12:53:59 GMT

Both Floor and Ceiling functions are supported in MySql.

Re: Problem with date in loading script

Peter_Cammaert — Tue, 05 Jan 2016 12:58:27 GMT

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

Re: Problem with date in loading script

tamilarasu — Tue, 05 Jan 2016 13:33:04 GMT

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

Re: Problem with date in loading script

Peter_Cammaert — Tue, 05 Jan 2016 13:44:14 GMT

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?

Re: Problem with date in loading script

tamilarasu — Tue, 05 Jan 2016 17:38:55 GMT

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).