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

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
stigchel
Partner - Master
Partner - Master

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
stigchel
Partner - Master
Partner - Master

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

miguelbraga
Partner - Specialist III
Partner - Specialist III

Hey there,

Please see my solution in attached file

Best regards,

D.A. MB

Anonymous
Not applicable
Author

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;

Anonymous
Not applicable
Author

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