topic Re: Date Function in QlikView

Date Function

Anonymous — Tue, 28 Oct 2014 17:33:02 GMT

Hi All,

When i am pulling the data(doing cross table) from the attached Excel, am not able to populate the Date in a proper format. Can any one help me to display the date in 'MM-DD-YYYY'.

Thanks!!

Re: Date Function

Anonymous — Tue, 28 Oct 2014 17:42:41 GMT

Try something like this in your load script :

date ( Date# ( YourDateField , 'DD/MM/YYYY' ) ,'MM-DD-YYYY') as DateField ,

Re: Date Function

Kushal_Chawda — Tue, 28 Oct 2014 17:45:19 GMT

Please see the attached file

Re: Date Function

ashfaq_haseeb — Tue, 28 Oct 2014 17:46:16 GMT

Hi,

Check this.

Temp:
CrossTable(Date, Data)
LOAD RICOH, 
     [41670.000000], 
     [41698.000000], 
     [41729.000000], 
     [41759.000000], 
     [41790.000000], 
     [41820.000000], 
     [41851.000000], 
     [41882.000000], 
     [41912.000000]
FROM
Date.xlsx
(ooxml, embedded labels, table is [Revised Stats], filters(
Remove(Row, RowCnd(CellValue, 1, StrCnd(null)))
));
NoConcatenate
Main:
Load Data,date(num(num#(Date,'#####.######')),'MM-DD-YYYY') as Date,RICOH Resident Temp;
DROP Table Temp;

Regards

ASHFAQ

Re: Date Function

Tue, 28 Oct 2014 17:51:40 GMT

Hi Ankit,

Can you attach the load sentence you use to pull this data?

Re: Date Function

MarcoWedel — Tue, 28 Oct 2014 22:15:54 GMT

Hi,

in order to avoid changing the script if the input table changes, you should use a more generic approach without hard coded date column names:

table1:
CrossTable (DateTemp, Value)
LOAD *
FROM [http://community.qlik.com/servlet/JiveServlet/download/643531-134441/Date.xlsx] (ooxml, embedded labels, table is [Revised Stats])
Where RecNo()<5;

Left Join
LOAD Distinct
  DateTemp,
  Date(Num#(DateTemp)) as Date
Resident table1;

DROP Field DateTemp;

hope this helps

regards

Marco