New to Qlik. Need help troubleshooting code where Qlik duplicates the time entered for old and new roles of employee. I need to get only hours worked for most recent role. Below is the code.
Thanks
if vReloadSource = 1 then
FOR EACH SourceFile IN FieldValueList('SourceFileName')
// Determine timesheet year and series of months to process (limit max to current month of the current year)
[Timesheet Year]:
LOAD
Num#([B]) AS TimesheetYear
FROM
'$(SourceFile)'
(ooxml, no labels, header is 7 lines, table is Settings)
WHERE RecNo() = 1;
LET vTimesheetYear = Peek('TimesheetYear', 0, 'Timesheet Year');
DROP TABLE [Timesheet Year];
LET vMaxMonth = 12;
IF vTimesheetYear > vCurrentYear THEN
LET vMaxMonth = 1;
ELSEIF vTimesheetYear = vCurrentYear THEN
LET vMaxMonth = $(vCurrentMonth);
END IF
[Timesheet Location]:
LOAD
[C] AS TimesheetLocation
FROM
'$(SourceFile)'
(ooxml, no labels, header is 7 lines, table is Settings)
WHERE RecNo() = 1;
LET vTimesheetLocation = Peek('TimesheetLocation', 0, 'Timesheet Location');
DROP TABLE [Timesheet Location];
[StaffData]:
Load *, ApplyMap('MapDeptNumber',FullDepartment) as Department;
LOAD
// H AS [Staff Name],
I AS [Employee ID],
if($(vTimesheetYear), K) AS [Staff Status],
J AS [Role Name],
L AS [FullDepartment],
if($(vTimesheetYear), M) AS [Manager Name],
ApplyMap('MapLocation', '$(vTimesheetLocation)') AS [Staff Location]
FROM
'$(SourceFile)'
(ooxml, no labels, header is 12 lines, table is Settings)
WHERE NOT IsNull([H]);
FOR i = 1 TO $(vMaxMonth)
LET vIterationMonthName = Lookup('Month Name', 'Month Number', $(i), 'Months');
[Raw.TimeData]:
LOAD
$(vTimesheetYear) AS [Year],
$(i) AS [Month],
If(IsNull(Lookup('Employee ID', 'Employee ID', [K], 'StaffData')),
Peek([Employee ID], -1),
Lookup('Employee ID', 'Employee ID', [K], 'StaffData')
) AS [Employee ID],
If(IsNull(Lookup('Employee ID', 'Employee ID', [K], 'StaffData')),
UPPER([C])
) AS [Code],
If(NOT IsNull([D]),
UPPER([D])
) AS [Activity Code],
If(NOT IsNull([E]),
[E]
) AS [Supported Location],
K AS [1],
L AS [2],
M AS [3],
N AS [4],
O AS [5],
P AS [6],
Q AS [7],
R AS [8],
S AS [9],
T AS [10],
U AS [11],
V AS [12],
W AS [13],
X AS [14],
Y AS [15],
Z AS [16],
AA AS [17],
AB AS [18],
AC AS [19],
AD AS [20],
AE AS [21],
AF AS [22],
AG AS [23],
AH AS [24],
AI AS [25],
AJ AS [26],
AK AS [27],
AL AS [28],
AM AS [29],
AN as [30],
AO as [31]
FROM
'$(SourceFile)'
(ooxml, no labels, header is 1 lines, table is $(vIterationMonthName));
StaffHoursCrossTable:
CrossTable(Day, Hours, 6)
LOAD *
RESIDENT [Raw.TimeData]
WHERE
RecNo() > 8
AND NOT IsNull([Code])
AND Match(UPPER([Code]),
'MERCY SHIPS VACATION', 'ROUTINE', 'ROUTINE IS HOURS',
'PROJECTS', 'PROJECT IS HOURS', 'NON IS HOURS', 'TOTAL', 'REMOTE WORKING') = 0
AND WildMatch(UPPER([Code]), 'OPEN *') = 0
AND (NOT IsNum([Code]) OR RecNo() = 6)
AND NOT [Employee ID] = [Code];
TimeData:
LOAD
//ApplyMap('MapLocation', [Location]) AS [Location],
[Employee ID],
[Code],
// [Role Name],
// [Department],
[Activity Code],
[Supported Location],
Date(Date#([Year] & '-' & [Month] & '-' & [Day], 'YYYY-M-D'), 'YYYY-MM-DD') AS [Work Date],
[Hours]
RESIDENT StaffHoursCrossTable
WHERE [Hours] > 0 and IsNum(Hours);
DROP TABLES
[Raw.TimeData],
StaffHoursCrossTable;
NEXT i;
NEXT SourceFile;
END IF
Exit Script;
