Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

bouderbc
New Contributor III

How to isolate a date between quotes in a column ?

How to isolate a date between quotes in a column  ?

All the date values of my column are between quotes , Telle me How to isolate the value between quotes

Example: Column: Date

Fields Text Format ' DD-MM-YYYY'  into  Result  DD-MM-YYY

' DD-MM-YYYY'  into DD-MM-YYY


Regards

Ali

1 Solution

Accepted Solutions

Re: How to isolate a date between quotes in a column ?

TextBetween(Datefield, chr(39), chr(39))

or

SubField(DateField, chr(39), 1)

-Rob

8 Replies
vvira1316
Valued Contributor II

Re: How to isolate a date between quotes in a column ?

Hi Ali,

Have you tried

Date#(TextDate, 'DD-MM-YYYY') this should result in your expecte output

Date#('31-12-2016', 'DD-MM-YYYY') as 31st of Dec, 2016 will be seen as 31-12-2016

Not sure ' DD-MM-YYYY'  space before DD, if so you can remove space using trim function.

If this is what you are not expecting then please share sample data/app.

BR,

Vijay

bouderbc
New Contributor III

Re: How to isolate a date between quotes in a column ?

Hi Vijay,

i have already tried Date#(TextDate, 'DD-MM-YYYY') but it doesn't work and the subfield function without success


i share an excel sample , so all the fields of the two columns are between quotes .


Please tell me if you have a solution


Thank you

Regards

Ali

Re: How to isolate a date between quotes in a column ?

TextBetween(Datefield, chr(39), chr(39))

or

SubField(DateField, chr(39), 1)

-Rob

vvira1316
Valued Contributor II

Re: How to isolate a date between quotes in a column  ?

Hi Ali,

As Rob mentioned, yes you can use Chr(39) for Single Quote. You can use PurgeChar, SubField, TextBetween, Date# for formatting. You have date in two formats (DD/MM/YYYY and DD-MM-YYYY) in the file you have provided so not sure if your source data is clean and consistent. In either case you can handle it in loading or in chart.

NoConcatenate
Tablemodele:
LOAD Date#(PurgeChar([Date creation], Chr(39))) as [Date creation]
,
Date#(PurgeChar([Date Extract], Chr(39))) as [Date Extract]
FROM
[..\Data\Tablemodele.xlsx]
(
ooxml, embedded labels, table is [Table modele])

BR,

Vijay

.

vvira1316
Valued Contributor II

Re: How to isolate a date between quotes in a column ?

date.PNGhere it is

NoConcatenate
Tablemodele:
LOAD Date(Date#(PurgeChar([Date creation], Chr(39)), 'DD-MM-YYYY'), 'DD-MM-YYYY') as [Date creation]
,
Date(Date#(PurgeChar([Date Extract], Chr(39)), 'DD-MM-YYYY'), 'DD-MM-YYYY') as [Date Extract]
FROM
[..\Data\Tablemodele.xlsx]
(
ooxml, embedded labels, table is [Table modele])
where FindOneOf([Date Extract], '-');
Concatenate
LOAD Date(Date#(PurgeChar([Date creation], Chr(39)), 'DD/MM/YYYY'), 'DD-MM-YYYY') as [Date creation]
,
Date(Date#(PurgeChar([Date Extract], Chr(39)), 'DD/MM/YYYY'), 'DD-MM-YYYY') as [Date Extract]
FROM
[..\Data\Tablemodele.xlsx]
(
ooxml, embedded labels, table is [Table modele])
where FindOneOf([Date Extract], '/');

bouderbc
New Contributor III

Re: How to isolate a date between quotes in a column ?

SubField(DateField, chr(39), 1) is the right solution !!! Thank you Rob

bouderbc
New Contributor III

Re: How to isolate a date between quotes in a column ?

Correct Thank you ! u're Great

bouderbc
New Contributor III

Re: How to isolate a date between quotes in a column ?

LOAD SubField([Date Extract],chr(39),1) as [Date Extract] ,

     SubField([Date creation], chr(39),1) as [Date creation],

From..