topic Re: Using Lefts and Rights in QlikView

Using Lefts and Rights

Eureka99 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Using Lefts and Rights

sunny_talwar — Fri, 20 Sep 2019 13:02:14 GMT

Try this

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

Re: Using Lefts and Rights

Eureka99 — Fri, 20 Sep 2019 13:10:48 GMT

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

Re: Using Lefts and Rights

sunny_talwar — Fri, 20 Sep 2019 13:17:00 GMT

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;

Re: Using Lefts and Rights

Eureka99 — Fri, 20 Sep 2019 13:23:40 GMT

Thanks Sunny, that worked!

Re: Using Lefts and Rights

rubenmarin — Sun, 22 Sep 2019 10:03:22 GMT

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