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: 
songzixian2016
Contributor III
Contributor III

How to do calculation and concatenate two tables?

I have two tables and would like to make it one:

table 1:

Month

HourEmployee
July1A
July2A
July3B
July4C
August2A
August8B
August4B
August1C

 

table 2:

MonthTotal
July20
August18

 

What I would like to get is below. I would like to have a calculated row for each month calculated by using Total from [table 2] for the corresponding month subtracted by {sum of hours} for that month in [table 1]

Month

HourEmployee
July1A
July2A
July3B
July4C
July20-sum(1+2+3+4) = 10Remaining Hours
August2A
August8B
August4B
August1C
August18-sum(2+8+4+1) = 3Remaining Hours

 

Could anyone help me with the loading script?

Thanks so much!

Labels (2)
2 Replies
zzyjordan
Creator II
Creator II

Hi, 

Please find my solution belowUntitled.jpgHope this helps

ZZ

lblumenfeld
Partner Ambassador
Partner Ambassador

This script will generate your result.

 

// First, load your data tables

[table 1]: Load * Inline [ Month, Hour, Employee July,1,A July,2,A July,3,B July,4,C August,2,A August,8,B August,4,B August,1,C ]; left Join ([table 1]) Load Month, Total Inline [ Month,Total July,20 August,18 ];
// Code to generate the table starts here [table 2]: NoConcatenate Load Month, Min(Total) - Sum(Hour) as Hour, 'Remaining Hours' as Employee Resident [table 1] Group By Month; [table 2]: Concatenate Load Month, Hour, Employee Resident [table 1]; drop Table [table 1];