Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
Hey there,
Please see my solution in attached file
Best regards,
D.A. MB
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;
Thank you for all your solutions. All the three solutions are working fine.