topic Re: Date format issue in QlikView

Date format issue

Anonymous — Thu, 22 Sep 2016 13:50:43 GMT

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%

15-Jan-17

80%

30-Mar-16

28-Apr-16

31-Jul-16

29-Sep-16

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

Re: Date format issue

stigchel — Thu, 22 Sep 2016 14:03:03 GMT

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

Re: Date format issue

miguelbraga — Thu, 22 Sep 2016 14:35:22 GMT

Hey there,

Please see my solution in attached file

Best regards,

D.A. MB

Re: Date format issue

Anonymous — Thu, 22 Sep 2016 14:43:57 GMT

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;

Re: Date format issue

Anonymous — Thu, 22 Sep 2016 15:00:34 GMT

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