Discussion board where members can learn more about Qlik Sense App Development and Usage.
I've 2 tables:
and want to create another table, which combines these tables to:
How do I do this in Qlik Sense load script?
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;
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:
Sum(PD_to_deliver) as PD,
'Contracted' as Status
Group By PID;
Sum(RangeSum(PDB)) as PD,
'Delivered' as Status
Group By PID;
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;