Unable to fetch date from excel files

[saradhi_it]

i have a historical csv files but  unable to fetch all the date's from the .csv files

Example:

actually for 1-15 the dates are like 1/9/2015

2/9/2015

3/9/2015

4/9/2015

5/9/2015

6/9/2015

7/9/2015

8/9/2015

9/9/2015

and  remaining dates are like 13/09/2015

14/09/2015

15/09/2015

16/09/2015

17/09/2015

18/09/2015

19/09/2015

20/09/2015

21/09/2015

22/09/2015

23/09/2015

24/09/2015

25/09/2015

26/09/2015

27/09/2015

28/09/2015

29/09/2015

Qlikview was reading 15-30 dates unable to(1-15) read these dates

i tried to use makedate(20&right(b_date,2),mid(b_date,4,2), left(b_datee,2)) as C__DATE, but it unable to read only 'D/M/YYYY' and reads only 'DD/MM/YYYY'

i used this condition  date(Date#(Datefield,'DD/MM/YYYY'),'ReqDateformat') as new date,but the in some other month's data if the date have  multiple entries like  for example 5/9/15 have multiple or more  entries  ,it was taking only one entry from the data

if i use the above condition

i tried to use makedate(20&right(b_date,2),mid(b_date,4,2), left(b_datee,2)) as C__DATE, but it unable to read

Message was edited by: pardha saradhi s

[sunny_talwar]

Can you try this:

Date(Date#(Datefield,'D/M/YYYY'),'D/M/YYYY') as NewDate

[saradhi_it]

NOPE.. its not working same result ,it was unable to read multiple date entries ...

[sunny_talwar]

Would you be able to share the script or the qvw you are working with?

[Pulkit_Thukral]

Try with this:

DATE(Alt(Date#(Datefield,'D/M/YYYY'),Date#(Datefield,'D/MM/YYYY')),'DD/MM/YYYY')

[saradhi_it]

nope ....

[er_mohit]

Hi,

If you have multiple date formats in one date field in entire table, so to get the all data set you required to call table with all date format using auto concatenate method. like

I have one table having ID, Product Name , Unit, Price , Date fields in which date field having format DD.MM.YYYY, DD/MM/YYYY,MM/DD/YYYY etc...

So in qlikview just follow below steps,

Table:

Load *,Date('Date','DD.MM.YYYY') as DateField from table;

Load *,Date('Date','DD/MM/YYYY') as DateField from table;

Load *,Date('Date','MM/DD/YYYY') as DateField from table;

Doing this you get all the result in one table.

Regards

Mohit

[qlikview979]

Hi

Try Like this

Assume "DATE" is your field name.

date(Alt(date(Date#(MakeDate(Year(DATE),Month(DATE),Day(DATE)),'M/DD/YYYY'),'DD/MM/YYYY'),Date(Date#(MakeDate(Year(DATE),Month(DATE),Day(DATE)),'DD/MM/YYYY')),'DD/MM/YYYY')) as DATE2

or

Date(date(MakeDate(Year(DATE),Month(DATE),Day(DATE)),'DD/MM/YYYY'),Date((MakeDate(Year(DATE),Month(DATE),Day(DATE))),'DD/MM/YYYY')) as DATE2