Date field with text data

[Dayna]

Hello,

I have a field that contains primarily date fields, but there are some entries that are also text, which I would like to keep.

When I input my data into QlikView, I get the numerical representation of the date, how do I change this to a standard date (i.e. DD/MM/YYYY) and still keep my text data?

I tried using date() but obviously I lose my text.

Please help!

Kind Regards,
Dayna

[sparur]

Hi, Dayana.

I don't understand your problem. 😞 What means "also text"? Do you have different data types (date and other string) in one field? Can you post your example qvw-file?

[Dayna]

Hello,

Yes, I have different data types in one column, unfortunately I cannot post the QVW but I can post the example data, i.e. within one column I have:

Not tested

Not tested

Not tested

18-Dec-08

22-Dec-08

22-Dec-08

09-Dec-08

09-Dec-08

10-Feb-09

09-Dec-08

16-Mar-09

10-Feb-09

09-Dec-08

16-Mar-09

But when this is inputted into QlikView, I get the numerical representation of the date, whereas I still need this in date format but would like to keep the text string..

Hope this helps.

Kind Regards,
Dayna

[#4]

Hi,

in the script you can try to write something like this (lets say that your column is named "Date"):

LOAD
IF(Date=DATE(Date), DATE(Date,'YYYY-MM-DD'), Date) AS Date2
FROM .......

Milda

[#5 · Accepted solution]

Hi Dayna,
Have you tried
like:

IF( ISNULL( Date('FieldName' ,'DD-MM-YYYY')),'FieldName',Date(Today(),'DD-MMM-YYYY')) as NewField_Name

in the Script While Loading the data

It will first check the field value is text if it a text then it will return Null
oterwise date

so you will get both text and date in the same field

Hope this is what you are looking for .

Thanks
Rahul

[sparur]

in your situation I can advise you 2 variant:
1) Divide the data - in one part will be the lines with the data type "Date", in another lines only text data (NotTested)

LOAD ..., IF(YourField = 'Nottested', '01-jan-1900', YourField) as YourField

FROM....

2) Convert the string when Loading data - "NotTested" = "unreal" date, for example, 01.01.1900

[Dayna]

That's perfect!!

Many thanks everyone for your help!!!