Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
leszekpiekielni
New Contributor III

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
Partner
Partner

Re: Sum based on values from 2 tables

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
Valued Contributor

Re: Sum based on values from 2 tables

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

leszekpiekielni
New Contributor III

Re: Sum based on values from 2 tables

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

Partner
Partner

Re: Sum based on values from 2 tables

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

leszekpiekielni
New Contributor III

Re: Sum based on values from 2 tables

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