Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Date format issue

Hi all,

I have one column called "Date" in Excel file. This Date field contains Dates and percentage value.

Issue - When I am loading this field into Qlik View, percentages are converting into date format. But I need to show into Qlik view same as in excel file i.e. percentage should show in %, Dates should show in date format.

Below is the sample data -

 

Date
70%
31-Jul-16
14-Feb-17
31-Dec-16
30-Dec-16
80%
80%
15-Jan-17
80%
80%
80%
30-Mar-16
28-Apr-16
31-Jul-16
29-Sep-16
29-Sep-16

Please check the attached sample file and Qlik View file. Can someone help me on this?

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Date format issue

You can use something like the following, last line is when you want both formats in one field

LOAD

if(num(Date)<=1,Num(Date)) as Percentage,

if(num(Date)>1,Date) as Date,

if(num(Date)<=1,Num(Date),Date(Date)) as Date_Percentage

FROM

View solution in original post

4 Replies
Highlighted
Partner
Partner

Re: Date format issue

You can use something like the following, last line is when you want both formats in one field

LOAD

if(num(Date)<=1,Num(Date)) as Percentage,

if(num(Date)>1,Date) as Date,

if(num(Date)<=1,Num(Date),Date(Date)) as Date_Percentage

FROM

View solution in original post

Highlighted
Specialist III
Specialist III

Re: Date format issue

Hey there,

Please see my solution in attached file

Best regards,

D.A. MB

Highlighted
Partner
Partner

Re: Date format issue

you can use alt function

you can use alt function also in caclulated Dimension (depending what you want)

test:
load * inline [
Date
70%
31-Jul-16
14-Feb-17
31-Dec-16
30-Dec-16
80%
80%
15-Jan-17
80%
80%
80%
30-Mar-16
28-Apr-16
31-Jul-16
29-Sep-16
29-Sep-16
]
;

tmp:
load
alt(date#(Date,'DD-MMM-YY'), Date, num(Date,'0%'))
Resident test;

drop table test;

Highlighted
Creator II
Creator II

Re: Date format issue

Thank you for all your solutions. All the three solutions are working fine.