build new data from loaded tables

[mic]

Hi,

I've 2 tables:

T1

id	v
1	5
2	10
3	15

T2

id	w
1	2
1	2
2	8

and want to create another table, which combines these tables to:

id	s	v
1	1	5
1	2	4
2	1	10
2	2	8

How do I do this in Qlik Sense load script?

Thx

[Arthur_Fong]

Where is S column coming from?

[mic]

s is the source table: T1 or T2.

[rubenmarin]

Hi, this can be done using a concatenate of both tables, using a group by to sum values of the same 1, values for 's' should be fixed (or using a bucle and a variable with table names), ie:

FinalTable:
LOAD id, Sum(v) as v, 1 as s
From [Table1Source] Group By id;

Concatenate(FinalTable)
LOAD id, Sum(w) as v, 2 as s
From [Table2Source] Group By id;

[mic]

I have multiple issues here:

1. Missing Values

I use Sum(RangeSum(v)) to fix this. Is this the right way?

2. Duplication of values:

My final table has the value from T1 (correct), sum of T1 and T2 instead of just sum(T2), and the single values of T2 with s not set. I can fix this by subtracting the value of T1 and filtering the single values. But how comes???

3. As in my sample there are ID which just exists in T1, and I want to omit them.

I need something similar to the SQL "Exists".

Here m actual code:

[PipeLine]:
Load
PID,
Sum(PD_to_deliver) as PD,
'Contracted' as Status
Resident deals
Where status='won'
Group By PID;

Load
PID,
Sum(RangeSum(PDB)) as PD,
'Delivered' as Status
Resident Booking
Group By PID;

Thanks

[rubenmarin]

Hi, I don't know what you mean with "missing values" but I don't think that rangesum in that way will solve it. Neither I understand the "and the single values of T2 with s not set" part

Just to guess a solution you can work with:

tmpPipeLine:
// first LOAD table2 to filter table1 after this one using Exists()
Load
PID,
Sum(RangeSum(PDB)) as PD,
'Delivered' as Status
Resident Booking
Group By PID;

Load
PID,
Sum(PD_to_deliver) as PD,
'Contracted' as Status
Resident deals
Where status='won' and Exists ('PD', PD_to_deliver)
Group By PID;

[PipeLine]:
LOAD
  PID,
  Sum(If(Status='Contracted', PD)) as PD_T1, //Table 1?
  Sum(PD) as PD_Total, // Sum of table 1 and table 2
  Concat(If(Status='Delivered', PD), ', ') as PD_SingleT2
Resident tmpPipeline
Group by PID;

DROP Table tmpPipeline;