Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

davidbalmer
Not applicable

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

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

Tags (1)
1 Solution

Accepted Solutions
davidbalmer
Not applicable

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

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

3 Replies
maxgro
Not applicable

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

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);

davidbalmer
Not applicable

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

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
Not applicable

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

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.