Load Date from Excel File

[#1]

Hi,

I am new to QlikView

I have data in my excel file in following date format

Dec-16

Dec-16

Dec-16

Jan-16

Feb-16

Dec-16

Dec-14

Dec-16

Dec-16

Dec-16

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)

[anbu1984]

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.

[jyothish8807]

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

KC

[SunilChauhan]

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

[engishfaque]

Dear Sid,

Try this one,

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

Kind regards,

Ishfaque Ahmed

[Anonymous]

Hi try like

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

[alkesh_sharma]

Try this

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

Here all Dec-16 will be clubbed together.

[nikhilgarg]

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

[#9]

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

[Anonymous]

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