Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
songzixian2016
New 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
Contributor II

Re: How to do calculation and concatenate two tables?

Hi, 

Please find my solution belowUntitled.jpgHope this helps

ZZ

lblumenfeld
Contributor

Re: How to do calculation and concatenate two tables?

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];