Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Break/fix

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;

1 Reply
Highlighted
Partner
Partner

Hi,

Even with this code example no one will be able to run it, except yourself 🙂

First of all check where the problem occurs on front-end or back end... Select one employee and check underlying data, then if you find problems in script - continue with fixing script, if not look into chart expressions...

You should be able easily filter out "most recent" position from your data set, anyway you're the only person who can troubleshoot it.

Hope this helps.

//Andrei