Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Sandip
Contributor II
Contributor II

How can we convert two date format (eg.'DMY' and 'MDY') in one single format ('DD/MM/YYYY').

Hi Team,

I have create sample data in excel sheet and i have put date data filed like in my excel sheet  'DD/MM/YYYY' format.

but after loading  data on Qlikview, Date filed it will consider Two format Like 'DD/MM/YYYY' and 'MM/DD/YYYY'

But i want Only one Single format Like 'DD/MM/YYYY'

below are the data;

Name dept date Payment
Sandip IT 15/05/2022 38715
Prasad Banking 8/4/2022 31212
Chetan IT 6/6/2022 42630
Rahul Pharma 25/05/2022 26211
Shubham Pharma 30/04/2022 38722
Kalu Farmer 10/3/2022 49255
Mahesh IT 11/3/2022 27317
Kaya Analystics 13/02/2022 59694
Saurabh Analystics 25/04/2022 46146
Pavan Chemical 13/4/2022 32599

 

I have Try diff way so many time but still problem not sort out.

eg.

date(alt(date(date,'DD/MM/YYYY'),date(date,'MM/DD/YYYY')),Date(date,'DD/MM/YYYY'))

date(num(Date(date)))

num(date#(date,'DD/MM/YYYY'))

DATE(date#(date,'DD/MM/YYYY'),'DD/MM/YYYY')

Date(Alt(Date#(date, 'DD/MM/YYYY'), Date#(date, 'MM/DD/YYYY')),'DD/MM/YYYY') as Date

 

Please help me to resolve the my problem 

 

Thanks 

Sandip Chaudhari 

8 Replies
salonicdk28
Creator II
Creator II

Hi,

please try the below expression in your script once-

Date(Floor(Alt(Num(date), Num(Date#(date,'MM/D/YYYY')), Num(Date#(date,'DD/MM/YYYY')))), 'DD/MM/YYYY') as NewDate

Thanks,

Saloni

Sandip
Contributor II
Contributor II
Author

Hi @salonicdk28 ,

It's Working but we are getting data like this ,

Name dept date NewDate Payment
Kaya Analystics 13/02/2022 13/02/2022 59694
Pavan Chemical 13/4/2022 13/04/2022 32599
Saurabh Analystics 25/04/2022 25/04/2022 46146
Shubham Pharma 30/04/2022 30/04/2022 38722
Sandip IT 15/05/2022 15/05/2022 38715
Rahul Pharma 25/05/2022 25/05/2022 26211
Chetan IT 6/6/2022 06/06/2022 42630
Kalu Farmer 10/3/2022 03/10/2022 49255
Mahesh IT 11/3/2022 03/11/2022 27317
Prasad Banking 8/4/2022 04/08/2022 31212

 

Here in the data we have date and NewDate filed. But sir i have put date format DD/MM/YYYY and  old date 10/03/2022 change like 03/11/2022. In this case old day is 11 as per i put date and after change date will be showing 03. so this we don't want .

we want as per old format as same in new also.

eg. old date 10/03/2022    and    NewDate   10/03/2022

 

Thank You for the support 

 

Thanks 

Sandip Chaudhari

salonicdk28
Creator II
Creator II

So, 

Kalu Farmer 10/3/2022
Mahesh IT 11/3/2022

are these in DD/MM format or MM/DD format, I thought these are in MM/DD hence I have changed those into DD/MM

salonicdk28
Creator II
Creator II

If you covert the date in the table directly, that is also working using below expression-

=Date(Date#(date,'DD/MM/YYYY'),'DD/MM/YYYY')

Sandip
Contributor II
Contributor II
Author

Yes sir All old date in DD/MM Format. we trying to change in Same as NewDate also DD/MM.

But when we are uploading on qlikview it will take fiv date as DD/MM and fiv date as MM/DD.

You can see the diff of both date

IT 15/05/2022 15/05/2022
Pharma 25/05/2022 25/05/2022
IT 6/6/2022 06/06/2022
Farmer 10/3/2022 03/10/2022

 

Thanks 

Sandip Chaudhari

Sandip
Contributor II
Contributor II
Author

Yes it's Working sir,

but fev date showing null

Name dept date NewDate Payment New
Kaya Analystics 13/02/2022 13/02/2022 59694 13/02/2022
Pavan Chemical 13/4/2022 13/04/2022 32599 13/04/2022
Saurabh Analystics 25/04/2022 25/04/2022 46146 25/04/2022
Shubham Pharma 30/04/2022 30/04/2022 38722 30/04/2022
Sandip IT 15/05/2022 15/05/2022 38715 15/05/2022
Rahul Pharma 25/05/2022 25/05/2022 26211 25/05/2022
Chetan IT 6/6/2022 06/06/2022 42630  
Kalu Farmer 10/3/2022 03/10/2022 49255  
Mahesh IT 11/3/2022 03/11/2022 27317  
Prasad Banking 8/4/2022 04/08/2022 31212  

 

And 

Sir if we are change format in one of the date like ,

25/05/2022 ---(DD/MM/YYYY)----Day-25 Month-05

After Converting format for same date Like --

05/25/2022---(MM/DD/YYYY)  -- Month-05 Day 25 

above eg no Change the day(25) and month (05) after changing the format.

 

But if we see  in another date cell like 

Rahul Pharma 25/05/2022 25/05/2022 26211 25/05/2022
Chetan IT 6/6/2022 06/06/2022 42630  
Kalu Farmer 10/3/2022 03/10/2022 49255  
Mahesh IT 11/3/2022 03/11/2022 27317  
Prasad Banking 8/4/2022 04/08/2022 31212  

 

for eg . 10/03/2022 format is DD/MM/YYYY as we put in excel.

her Day - 10 and Moth 03 

But when we change format for the same for all that time  last four date will be change but wrongly.

eg. 03/10/2022 Day - 03 and Moth 10 (but it's wrong )

so this change we are trying to change .

 

Thank You for the support 

 

Thanks 

Sandip chaudhari

 

 

 

salonicdk28
Creator II
Creator II

Can you try in front end in the table dimension itself , it should work there and in the script editor we have formats defined in the main page so it is creating an issue there in back end

=Date(Date#(date,'DD/MM/YYYY'),'DD/MM/YYYY')

Hope you get the scenario

Thanks,

Saloni (she/her/hers)

Sandip
Contributor II
Contributor II
Author

Not Working as front as back end 

 

Thank You for support

Thanks 

Sandip chaudhari