Qlik Community

Ask a Question

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
March 11, 2PM EST: Do More with Qlik - Getting Started wtih Qlik Sense SaaS - Redux. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mic
Contributor
Contributor

build new data from loaded tables

Hi,

I've 2 tables:

T1

idv
15
210
315

 

T2

idw
12
12
28

 

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

idsv
115
124
2110
228

 

How do I do this in Qlik Sense load script?

Thx

Labels (1)
5 Replies
Arthur_Fong
Partner
Partner

Where is S column coming from?
mic
Contributor
Contributor

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
Contributor
Contributor

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;