Using Lefts and Rights

[Eureka99]

Hi,

I'm relatively new to Qlik view.

I have a date field which  is causing issues as it will not link to my Fiscal Calendar. On investigation it seem the date has imported in reverse order "YYYYMMDD"

Right(OAORDT,2) & '/' & Right(Left(OAORDT,6),2) & '/' & Left(OAORDT,4) as Date1

I've tried using the above left and rights to fix this, but its not working. Can someone help explain where I've gone wrong or the correct way to do this in Qlikview

Thanks 

[sunny_talwar]

Try this

Date(Date#(OAORDT, 'YYYYMMDD'), 'DD/MM/YYYY') as Date1

[Eureka99]

Thanks for this, It didn't work though.

To give you the full details I'm importing from and OLE DB for SQL, my import code is:

OOHEAD:
SQL SELECT 
OAORNO as OrderNo,
OAORDT,
Date(Date#(OAORDT, 'YYYYMMDD'), 'DD/MM/YYYY') as Date1,
OASMCD as Salesman
FROM Movex.dbo.OOHEAD;

This has given me the following error on reload:

OleDb error

Connector reply error: ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: 'Date#' is not a recognized built-in function name.

OOHEAD:
SQL SELECT 
OAORNO as OrderNo,
OAORDT,
Date(Date#(OAORDT, 'YYYYMMDD'), 'DD/MM/YYYY') as Date1,
OASMCD as Salesman
FROM Movex.dbo.OOHEAD

[sunny_talwar · Accepted solution]

Try this instead

OOHEAD:
LOAD OrderNo,
     Date(Date#(OAORDT, 'YYYYMMDD'), 'DD/MM/YYYY') as Date1,
     OAORDT,
     Salesman;
SQL SELECT 
OAORNO as OrderNo,
OAORDT,
OASMCD as Salesman
FROM Movex.dbo.OOHEAD;

[Eureka99]

Thanks Sunny, that worked!

[rubenmarin · Accepted solution]

Hi, if your question is solved please check Sunny's answer as an accepted solution.