Date Function

[Anonymous]

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!!

[Anonymous]

Try something like this in your load script :

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

[Kushal_Chawda · Accepted solution]

Please see the attached file

[ashfaq_haseeb]

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

[#5]

Hi Ankit,

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

[MarcoWedel]

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