topic Re: convert in QlikView

convert

smilingjohn — Wed, 25 Nov 2020 16:16:04 GMT

I have a Excel sheet , in which the month names are in text like Jan, feb , mar and so on

How do i convert this to Date ?

Re: convert

vikasmahajan — Fri, 29 Jul 2016 06:58:09 GMT

Put this in script

Date(date#(month,'YYYYMM'),'DD-MM-YYYY') as PostingDate

Vikas

Re: convert

sunny_talwar — Fri, 29 Jul 2016 07:00:25 GMT

May be like this:

Month(Date#(Captialize(month), 'MMM')) as Month

Re: convert

Chanty4u — Fri, 29 Jul 2016 07:01:07 GMT

try

Date(Date#(Datefield,'MMMDDYYYY'),'YY-MM-DD') AS newdate

Date(Date#(Datefield,'urdatefieldformat'),'reuireddateformat') AS newdate

Re: convert

yadhunandhanan — Fri, 29 Jul 2016 07:04:18 GMT

Hi John,

How about this,

=Date(Date#('15-Jan-16','DD-MMM-YY'),'DD-MM-YY')

Replace '15-Jan-16' as your date field.

Hope its help.

Re: convert

smilingjohn — Fri, 29 Jul 2016 07:09:23 GMT

Hi vikas thanks

In a excel sheet i have Months vertical

Below is the example of the column under excel sheet.

ID ,Dept,Amount, Year,Jan,Feb,Mar,Apr,May....... Dec

1, Soap, 12, 2010 23, 24,1,12,.,.,.,.,.

2 ,Cosmatics,100,, 2010 ,11,22,2

This is how i have the excel sheet , where year has no problem only month is horizontal and in text format

How do i deal with this ,

Regards

Re: convert

Fri, 29 Jul 2016 07:10:25 GMT

Try thissmilingjohn

=date(Month,'DD-MM-YYYY') as DATE

Re: convert

sunny_talwar — Fri, 29 Jul 2016 07:10:36 GMT

You need to implement a CrossTable Load here:

The Crosstable Load

Re: convert

Chanty4u — Fri, 29 Jul 2016 07:11:42 GMT

use Crosstable for that then u can create date filed as abve mentoned

Re: convert

vikasmahajan — Fri, 29 Jul 2016 07:15:18 GMT

After implementation of cross table , load script with Date(date#(month,'YYYYMM'),'DD-MM-YYYY') as PostingDate

This should solve your problem

Vikas