topic How to do calculation and concatenate two tables? in QlikView

How to do calculation and concatenate two tables?

songzixian2016 — Sat, 16 Nov 2024 04:51:48 GMT

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

table 1:

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

table 2:

Month	Total
July	20
August	18

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	Hour	Employee
July	1	A
July	2	A
July	3	B
July	4	C
July	20-sum(1+2+3+4) = 10	Remaining Hours
August	2	A
August	8	B
August	4	B
August	1	C
August	18-sum(2+8+4+1) = 3	Remaining Hours

Could anyone help me with the loading script?

Thanks so much!

Re: How to do calculation and concatenate two tables?

zzyjordan — Thu, 03 Jan 2019 02:45:11 GMT

Hi,

Please find my solution belowHope this helps

Re: How to do calculation and concatenate two tables?

lblumenfeld — Thu, 03 Jan 2019 02:53:46 GMT

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