Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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.
Try this
Date(Date#(Report_date,'DD-MMM-YYYY'),'DD-MMM-YYYY') as Req_Date
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;
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;
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
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
Use Crosstable Load
By using the crosstable you can get the result