Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Using Lefts and Rights

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 

2 Solutions

Accepted Solutions
Highlighted

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;

View solution in original post

Highlighted

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

View solution in original post

5 Replies
Highlighted

Try this

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

 

Highlighted
Contributor II
Contributor II

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

 

Highlighted

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;

View solution in original post

Highlighted
Contributor II
Contributor II

Thanks Sunny, that worked!

Highlighted

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

View solution in original post