Issue with Date

[#1]

Hi All, I have an excel file containing attendance records of all employees in the format specified below.

Employee #	Names	Team Name	Designation	Work status	01-Jan-16 Fri	02-Jan-16 Sat	03-Jan-16 Sun	04-Jan-16 Mon	05-Jan-16 Tue	06-Jan-16 Wed	07-Jan-16 Thu
1	Priya	IT	Analyst	Active	P	P	P	P	P	P	P
2	Priyanka	IT	Analyst	Active	A	A	A	A	P	P	P
3	Monika	Finance	Analyst	Active	P	P	P	P	P	P	P
4	Sarvesh	Admin	Analyst	Active	P	P	P	P	P	P	P

And I want it to load it in below format

Employee #	Names	Team Name	Designation	Work status	Date	Att_Value
1	Priya	IT	Analyst	Active	01/01/2016	P
2	Priyanka	IT	Analyst	Active	01/01/2016	A
3	Monika	Finance	Analyst	Active	01/01/2016	P
4	Sarvesh	Admin	Analyst	Active	01/01/2016	P
1	Priya	IT	Analyst	Active	02/01/2016	P
2	Priyanka	IT	Analyst	Active	02/01/2016	A
3	Monika	Finance	Analyst	Active	02/01/2016	P
4	Sarvesh	Admin	Analyst	Active	02/01/2016	P
1	Priya	IT	Analyst	Active	03/01/2016	P
2	Priyanka	IT	Analyst	Active	03/01/2016	A
3	Monika	Finance	Analyst	Active	03/01/2016	P
4	Sarvesh	Admin	Analyst	Active	03/01/2016	P
1	Priya	IT	Analyst	Active	04/01/2016	P
2	Priyanka	IT	Analyst	Active	04/01/2016	A
3	Monika	Finance	Analyst	Active	04/01/2016	P
4	Sarvesh	Admin	Analyst	Active	04/01/2016	P
1	Priya	IT	Analyst	Active	05/01/2016	P
2	Priyanka	IT	Analyst	Active	05/01/2016	P
3	Monika	Finance	Analyst	Active	05/01/2016	P
4	Sarvesh	Admin	Analyst	Active	05/01/2016	P
1	Priya	IT	Analyst	Active	06/01/2016	P
2	Priyanka	IT	Analyst	Active	06/01/2016	P
3	Monika	Finance	Analyst	Active	06/01/2016	P
4	Sarvesh	Admin	Analyst	Active	06/01/2016	P
1	Priya	IT	Analyst	Active	07/01/2016	P
2	Priyanka	IT	Analyst	Active	07/01/2016	P
3	Monika	Finance	Analyst	Active	07/01/2016	P
4	Sarvesh	Admin	Analyst	Active	07/01/2016	P

But the issue I am facing is with the date column. It is getting loaded in number format. I tried converting it into date format using date and date# function but couldn't achieve desired result.

I need your suggestions.

Thanks in advance

-Priyanka

[#2]

Hi Nagaraj,

When I am trying to load my data, its in this format

Attendence_Staging:
CrossTable([Report Date], Attendence_Value, 5)
LOAD  Num#([Employee #]) as [Employee No],
Names as [Employee Names],
[Team Name],
Designation,
[Work status],
[42370],
[42371],
[42372],
[42373],
[42374],
[42375],
[42376]

FROM $(vAttendence)Attendance tracker UK Ops.xlsx
(ooxml, embedded labels, table is [Jan - Dec 2016]) ;

Below is the screenshot of Report Date field

My QV date format is

SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';

when I am using below script

Data:
Load *,
Date(Date#(Left([Report Date],7) & '2016','DD/MM/YYYY'),'DD-MM-YYYY') as Date
Resident Attendence_Staging;

I am getting Date field as '-'.

Please help

-Priyanka

[tresesco]

Or is it - you are trying to reply from your Inbox? Inbox doesn't have that option, try to open the thread in browser separately, you should get to attach.

[#4]

Tresesco- Yes I was trying to reply from inbox and that's the reason I was not getting attach option. Thanks for notifying it. I have attached sample excel file for your reference.

[sasikanth]

Try this

Date(Date#(Report_date,'DD-MMM-YYYY'),'DD-MMM-YYYY') as Req_Date

[tamilarasu]

Priyanka,

Try like below,

Tmp:
CrossTable([Report Date], Attendence_Value, 5)
LOAD  Num#([Employee #]) as [Employee No], 
Names as [Employee Names], 
[Team Name], 
Designation, 
[Work status], 
[42370], 
[42371], 
[42372], 
[42373], 
[42374], 
[42375], 
[42376]
FROM $(vAttendence)Attendance tracker UK Ops.xlsx
(ooxml, embedded labels, table is [Jan - Dec 2016]) ;

Data:
Load *,
Date(Num#([Report Date])) as Date
Resident Attendence_Staging;

Drop Field [Report Date];
Drop Table Tmp;

[tresesco]

Temp:

CrossTable(Date, Att_Value, 5) LOAD [Employee #],

     Names,

     [Team Name],

     Designation,

     [Work status],

     [42370],

     [42371],

     [42372],

     [42373],

     [42374],

     [42375],

     [42376]

FROM

(ooxml, embedded labels, table is Sheet1);

Load

	Names,
	[Employee #],
	[Team Name],

     Designation,

     [Work status],

     Date(PurgeChar(Date,'[]')) as Date

Resident Temp     ;

Drop Table Temp;

[MarcoWedel]

Hi,

in order to avoid hard coded date values in your script (e.g. for changing source files) you could try like:

table1:
CrossTable (DateTemp, Value, 6)
LOAD Evaluate([Employee #]) as Employee#, *
FROM [https://community.qlik.com/servlet/JiveServlet/download/977302-211779/Sample.xlsx] (ooxml, embedded labels, table is Sheet1);

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

DROP Fields [Employee #], DateTemp;

hope this helps

regards

Marco

[#9]

Hi Marco,

Thanks for your reply.

Can you please help me out in understanding this script. i.e. "LOAD Evaluate([Employee #]) as Employee#, *" why we have used Evaluate() in this script.

Thanks in advance

[sujeetsingh]

Use Crosstable Load

[Anonymous]

By using the crosstable you can get the result