topic Re: Load Date from Excel File in QlikView

Load Date from Excel File

Wed, 31 Dec 2014 01:12:39 GMT

Hi,

I am new to QlikView

I have data in my excel file in following date format

Dec-16

Jan-16

Feb-16

Dec-16

Dec-14

Dec-16

When I load the data in QV

I get data in Text

44355

43554

44455

Desired Output - I want to plot a graph counting values of X Axis on in Ascending Order

Please advise.

Below is the current output (instead I want output in date order)

Re: Load Date from Excel File

anbu1984 — Wed, 31 Dec 2014 02:41:18 GMT

Your X-axis is already ordered by date. If you want to change the format of date, then in script use Date function to change to desired format

Load Date(Date_field,'MMM-YY') As Date_field,

....

From ExcelFile

If you want same order as in your source, then select Load order on your date field in Sort tab.

Re: Load Date from Excel File

jyothish8807 — Wed, 31 Dec 2014 03:10:44 GMT

Hi Sid,

Make some changes in your script as follows:

Load

Date(Date_Field,'MMM-DD') as Date_Field,

from <>

Now you can use the same filed in your chart.

Hope it helps.

Regards

Re: Load Date from Excel File

SunilChauhan — Wed, 31 Dec 2014 03:11:24 GMT

Load Date(MonthName(Date_field),'MMM-YY') As Date_field,

....

From ExcelFile

it will remove duplicate values also like DEC-16,DEC-16,DEC-16,DEC-16,DEC-16

Re: Load Date from Excel File

engishfaque — Wed, 31 Dec 2014 04:18:07 GMT

Dear Sid,

Try this one,

Date(Num#(YourFieldName),'DD-MMM-YYYY')

Kind regards,

Ishfaque Ahmed

Re: Load Date from Excel File

Anonymous — Wed, 31 Dec 2014 05:13:56 GMT

Hi try like

Date(DateField,'MMM-YY') as DateField

Re: Load Date from Excel File

alkesh_sharma — Wed, 31 Dec 2014 05:52:19 GMT

Try this

Date(<Datefiled>,'MMM-DD') as Date

Here all Dec-16 will be clubbed together.

Re: Load Date from Excel File

nikhilgarg — Wed, 31 Dec 2014 05:59:10 GMT

HEy,

You can try like this:

Load:

Date('[your date field name]' , 'DD-MM-YYYY') as Date,

From .....

Order By '[your date field name]' ASC ;

Thanks

Re: Load Date from Excel File

Wed, 31 Dec 2014 06:16:46 GMT

Hi Sid

When data is loaded from excel to qvw, qlikview reads it as a string, therefore you have to tell qlikview to interpret this data into a particular format that you wish, in this case 'date format,.

The two particular functions that you can use amongst others are, Formatting function- Date() and Interpretation function-Date#().

This is how it works.

First the Interpretation function takes a string and create a correct date serial number, which is what u have in your chart(41883,41944,...), then the formatting function will convert these date serial number into dates,months, years,... depending on your requirements. therefore in your scripts include this:

Date(Date#(datefield,'DD/MMM/YYYY')) AS Date, assumption is that your grain is in date, otherwise

Month(Date#(datefield,'DD/MMM/YYYY')) ) AS Month.

Hope this helps.

Kind Regards

Dlamini

Re: Load Date from Excel File

Anonymous — Wed, 22 Aug 2018 11:42:53 GMT

I tried this and it works for me, thank you.