Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am trying to do IncrementalLoad and want to store my MaxDate into a QVD file.
My orginial QVD with all the data has a field called CreateDate
CreateDate is like 2014-05-22 10:00:00
When i want to store this
I select Max(CreateDate) as CreateDate into a QVD
But when i load my QVD it is empty.
Like it doesnt know the format or something=?
Try like:
LOAD Max(Date#(CreateDate,'YYYY-MM-DD hh:mm:ss')) as MaxIncrementalDate
If i just load all the dates without a Max i can see the dates.
So why does it not work with max?
Possibly a format issue. Max() would need a numeric field (proper date field actually holds a numeric value). When you load without max, how does the data look like?
My CreateDate is like this YYYY-MM-DD hh:mm:ss
I try something like this:
MaxIncrementalDate:
LOAD Date#(Date(Max(CreateDate),'YYYY-MM-DD hh:mm:ss')) as MaxIncrementalDate
FROM
(qvd);
Store MaxIncrementalDate into 'D:\QlikviewLoad\QVD\MaxIncrementalLoadDate.qvd';
//
Date:
LOAD MaxIncrementalDate as MaxIncrementalDate
FROM
(qvd);
LET vMaxIncrementalLoadDate = peek('MaxIncrementalDate',0,'Date');
If i load without the max, i get all the dates and can see data. But i only want the Max
MaxIncrementalDate:
LOAD CreateDate as MaxIncrementalDate
FROM
(qvd);
Store MaxIncrementalDate into 'D:\QlikviewLoad\QVD\MaxIncrementalLoadDate.qvd';
//
Date:
LOAD MaxIncrementalDate as MaxIncrementalDate
FROM
(qvd);
LET vMaxIncrementalLoadDate = peek('MaxIncrementalDate',0,'Date');
This is working, but then i get all the dates - If i use Date# it doesnt work.
Try like:
LOAD Max(Date#(CreateDate,'YYYY-MM-DD hh:mm:ss')) as MaxIncrementalDate
Then it gives me the numeric value of the date.
But i need the string cause im selecting data from and SQL connection where my createdate is a date/string characther
This version works!
MaxIncrementalDate:
LOAD Max(Date#(CreateDate,'YYYY-MM-DD hh:mm:ss')) as MaxIncrementalDate
FROM
(qvd);
Store MaxIncrementalDate into 'D:\QlikviewLoad\QVD\MaxIncrementalLoadDate.qvd';
//
Date:
LOAD Date(MaxIncrementalDate,'YYYY-MM-DD hh:mm:ss') as MaxIncrementalDate2
FROM
(qvd);
LET vMaxIncrementalLoadDate = peek('MaxIncrementalDate',0,'Date');
Thanks!
LOAD Date(Max(CreateDate),'YYYY-MM-DD hh:mm:ss') as MaxIncrementalDate ?
Wherever you store that numeric value can be converted to string like date using date(), like:
Date($(variable), 'YYYY-DD-MM') , or for a field
Date(DateField, 'YYYY-DD-MM') , or directly during the load, like
LOAD Date(Max(Date#(CreateDate,'YYYY-MM-DD hh:mm:ss')) , 'YYYY-DD-MM') as MaxIncrementalDate