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: 
Not applicable

Load Date from Excel File

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)

9 Replies
anbu1984
Master III
Master III

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
Master II
Master II

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

Best Regards,
KC
SunilChauhan
Champion
Champion



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

Sunil Chauhan
engishfaque
Specialist III
Specialist III

Dear Sid,

Try this one,

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

Kind regards,

Ishfaque Ahmed

Anonymous
Not applicable
Author

Hi try like

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


alkesh_sharma
Creator III
Creator III

Try this

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

Here all Dec-16 will be clubbed together.

nikhilgarg
Specialist II
Specialist II

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

Not applicable
Author

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
Not applicable
Author

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