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

How to isolate a date between quotes in a column ?

bouderbc — Wed, 27 Sep 2017 21:46:14 GMT

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

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

vvira1316 — Wed, 27 Sep 2017 21:59:14 GMT

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

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

bouderbc — Wed, 27 Sep 2017 22:24:43 GMT

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 ?

rwunderlich — Wed, 27 Sep 2017 22:31:24 GMT

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

SubField(DateField, chr(39), 1)

-Rob

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

vvira1316 — Wed, 27 Sep 2017 22:48:22 GMT

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

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

vvira1316 — Wed, 27 Sep 2017 22:53:34 GMT

here 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], '/');

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

bouderbc — Wed, 27 Sep 2017 22:57:06 GMT

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

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

bouderbc — Wed, 27 Sep 2017 23:00:53 GMT

Correct Thank you ! u're Great

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

bouderbc — Wed, 27 Sep 2017 23:08:12 GMT

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

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

From..