topic AW:Date issue in QlikView

Date issue

Mon, 07 Mar 2011 18:28:19 GMT

Sorry guys for this simple issue.

I have 2 txt file where the date fields have different format. Itlooks like

- 12/10/10 in the first one

- "2010/10/12 00:00:00" in the second

I'd like to concatenate both files with a unique date fields in 'DD/MM/YYYY' format.

How to achieve this ?

AW:Date issue

brenner_martina — Mon, 07 Mar 2011 18:32:18 GMT

Hi JJ,

you have to use the floor function:

First date: Load Date#(Datefield1,'DD/MM/YY') As DateKey, ...

Second date: Load Floor(Date#(Datefield2,'YYYY/MM/DD') As DateKey, ...

AW:Date issue

Mon, 07 Mar 2011 18:40:42 GMT

Hi Martina

Don't work !

Here a sample of my data(for the second)

"Code Entite comptable" "Date Comptable" "Nombre de lots"
"010040" "2010/10/11 00:00:00" "180"
"010040" "2010/10/12 00:00:00" "269"
"010040" "2010/10/13 00:00:00" "333"
"010040" "2010/10/14 00:00:00" "258"
"010040" "2010/10/15 00:00:00" "239"
"010040" "2010/10/16 00:00:00" "124"

Maybe another idea ?

AW:Re: AW:Date issue

brenner_martina — Mon, 07 Mar 2011 18:48:50 GMT

Hi JJ,

This works:

Data:
LOAD * INLINE [
"Code Entite comptable", "Date Comptable", "Nombre de lots"
010040, 2010/10/11 00:00:00, 180
010040, 2010/10/12 00:00:00, 269
010040, 2010/10/13 00:00:00, 333
010040, 2010/10/14 00:00:00, 258
010040, 2010/10/15 00:00:00, 239
010040, 2010/10/16 00:00:00, 124
];

Left Join (Data)
Load *,
Floor(Date#("Date Comptable",'YYYY/MM/DD hh:mm:ss')) As DateNew
Resident Data;

AW:Re: AW:Date issue

Mon, 07 Mar 2011 18:55:17 GMT

Yep Martina ! I forgot hh:mm:ss.

Thanks for your time