topic build new data from loaded tables in App Development

build new data from loaded tables

mic — Sat, 16 Nov 2024 03:35:18 GMT

Hi,

I've 2 tables:

id	v
1	5
2	10
3	15

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

Re: build new data from loaded tables

Arthur_Fong — Wed, 15 Jan 2020 00:50:32 GMT

Where is S column coming from?

Re: build new data from loaded tables

mic — Thu, 16 Jan 2020 13:08:26 GMT

s is the source table: T1 or T2.

Re: build new data from loaded tables

rubenmarin — Thu, 16 Jan 2020 14:28:54 GMT

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;

Re: build new data from loaded tables

mic — Fri, 17 Jan 2020 16:50:41 GMT

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

Re: build new data from loaded tables

rubenmarin — Mon, 20 Jan 2020 08:47:44 GMT

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;