Format a text field to a YYYY/MM/DD date field

[Anonymous]

Hi:

I have a tiny sample here in which I am ultimately trying to extract the YEAR, MONTH, DAY  out of a comma-delimited source.  The source has the following three fields:

DogName

OwnerName

Cost

PurchaseDate

The PurchaseDate field in each record starts with a + sign and looks like; +19950610

DogName,OwnerName,Cost,PurchaseDate

Clancy,David,500.00,+19950610

Casey,Alec,650.00,+20010331

Fraggle,Nadine,700.00,+20040201

I can remove the + sign with the following script but cannot get any further in rendering the field as a Date and then in the attempt to extract the Year, Month and Day as mentioned.   There is so much information in the Community on how to do it but I have had no success with any.  Though my sample is tiny, it represents an issue with a much larger file in the hundreds of thousands of records.  Any help in constructing the corrrect script would be most appreciated.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='YYYY/MM/DD';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

[Dogs]:

LOAD DogName,

          OwnerName,

          Cost,    

         //strip out the leading + sign fromn the PurchaseDateField

     KeepChar(PurchaseDate,'0123456789')as DateTrim

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Thank you very much

[maxgro]

try with

date(date#(right('+19950610',8), 'YYYYMMDD'))

so your script should be

[Dogs]:

LOAD DogName,

          OwnerName,

          Cost,  

    date(date#(right(PurchaseDate,8), 'YYYYMMDD')) as NewDate,

        //strip out the leading + sign fromn the PurchaseDateField   

    KeepChar(PurchaseDate,'0123456789')as DateTrim

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

[Anonymous · Accepted solution]

Hi Massimo:

And thanks for your scripting information.  It works perfectly and now allows me to take this Sunday off.  I really appreciate your quick response and the QlikView Community.

You might tell me how I mark this question as "answered" as I cannot find the method to do so.

Best regards

Dave

[maxgro]

You can close discussions by giving Correct (max to one answer) and Helpful (if I'm not wrong max to 2 answers) answers to the posts which are useful for you.  It helps others in finding answers for similar scenarios.