Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Sum based on values from 2 tables

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

1 Solution

Accepted Solutions
lalphonso
Partner - Contributor III
Partner - Contributor III

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.

View solution in original post

4 Replies
vamsee
Specialist
Specialist

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

Anonymous
Not applicable
Author

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

lalphonso
Partner - Contributor III
Partner - Contributor III

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.

Anonymous
Not applicable
Author

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