Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Hi, if your question is solved please check Sunny's answer as an accepted solution.
Try this
Date(Date#(OAORDT, 'YYYYMMDD'), 'DD/MM/YYYY') as Date1
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
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;
Thanks Sunny, that worked!
Hi, if your question is solved please check Sunny's answer as an accepted solution.