Unable to apply datetime transformations in the storage block

[akaradhya]

Hi All, 

I have 5 tables that I am moving from MySQL to Snowflake, and I am going through Landing->Storage->Transform->DataMart process.

In one of the tables, I have 2 columns namely ShipDate (DATETIME) and DueDate (DATETIME). Using these 2 fields I am trying to find the delay in shipment by finding the difference between these 2 fields by adding a new column called Shipment_status  (STRING(15)). Also, I am adding another column called Shipment_bucket (STRING(50)) to see in which buckets does the shipment status fall into. 

Expression for Shipment_status: $IFF(${ShipDate}-${DueDate} <=0, 'on-time', 'delayed')

Expression for Shipment_bucket: 

$IFF(${ShipDate}-${DueDate} >0 AND ${ShipDate}-${DueDate}<=1, '1-day Delay',
$IFF(${ShipDate}-${DueDate} >1 AND ${ShipDate}-${DueDate}<=2, '2-day Delay',
$IFF(${ShipDate}-${DueDate} >2 AND ${ShipDate}-${DueDate}<=3, '3-day Delay',
$IFF(${ShipDate}-${DueDate}>3, ' > 3-day Delay','On-time'))))

These two expressions are working when they are applied in the landing zone. But when I apply the same in the storage zone it gives me an error that UNKNOW Error: Numeric value cannot be handled. 

I tried to convert both shipdate and duedate using date() and datetime() function. 

Also, in the storage section any transformation involving number columns are working. 

Kindly suggest if I am missing something. 

Thank you. 

[TimGarrod · Accepted solution]

Hi,   In Qlik Cloud Data Integration it is important to understand WHERE the transformations are performed in each task you create in your pipeline. 
Landing -   When adding expressions in the Landing task, transformations are executed before the data is landed into your cloud data platform.   These transformations / expressions are executed in the gateway and leverage SQLLITE based SQL.    In general, these transformations should be designed as necessary for use cases like obfuscating data before it lands in the target.(eg. 256 bit hash a SSN, or only load the last 4 digits of a phone number).

Storage, Transform, Data Mart  - When adding expressions in the Storage / Transform / Data Mart task the transformations are executed in a ELT manner - in your target data platform.   These expressions should be written using the SQL of your target platform.  (e.g   SQL that would run in Snowflake / BigQuery / Databricks etc.).  

Since there is a dialect difference between LANDING and the other tasks - this is likely why your transform is not working correctly. A good way to test is, on the DataSets screen - select your dataset, click the ... and you can see the generated code.  There you can pull out the full load SQL - and run the select to check the syntax / data in your target platform.