Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello colleagues,
I need to create a Pivot Table that will show salaries per pay period. I have two tables, first:
payroll_number, region, department, split, date
and 2nd as:
date, payroll_number, salary
the table should look like
Date1, Date 2, Date3
Department split*Sum(value) split*Sum(value) split*Sum(value)
I joined the tables on payroll_number but the sum(value) does not show the correct value. It seems like I need to match the dates in both tables somehow?
Any help would be highly appreciated!
Thanks,
Leszek
Hi Leszek,
Yes you can combine the two fields using
1 - either directly with a delimiter (e.g. field1 + '|' + field 2) on each table as a linking key
2 - using an autonumber / autonumberhash function (https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/CounterFunctio...)
e.g.
Load
...
autonumberhash256(PayrollNumber, num(date(paydate, 'yyyymmdd'))) as %linkKey
...
Regards,
LA.
Assuming that
I--> your first table is EmployeeDetails and 2nd as Salary Details.
II--> Date in Second Table is Pay Date.
Can you please explain what does date mean in EmployeeDetails?
or please attach some sample data to understand the scenario further
HI Vamsee,
Thank you for looking into it and I am sorry I probably have not explained it correctly in the original post.
The first table Departments contains indeed employee cost code/ department history and 2nd table "Salaries" salary details.
The common values for both are the employee reference (payroll number, EMPREF) and pay date (date, ORD5).
As employees might move from one department to another, the date field show department value for each pay date and salary table salary paid in the same period.
Is there a way to link both tables on both values employee reference and pay date to sum salary value by a department by and by date?
Thanks,
Leszek
Hi Leszek,
Yes you can combine the two fields using
1 - either directly with a delimiter (e.g. field1 + '|' + field 2) on each table as a linking key
2 - using an autonumber / autonumberhash function (https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/CounterFunctio...)
e.g.
Load
...
autonumberhash256(PayrollNumber, num(date(paydate, 'yyyymmdd'))) as %linkKey
...
Regards,
LA.
Hi Leslie,
Thank you, it worked. I have concatenated both payroll number and date before but without delimiter and it did not work. Strangely the date format dropped from one of the tables when concatenated but I fiddled with the formatting and it works now.
Thank you for your help.
Leszek