Qlik Community

Qlik Sense App Development

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

Highlighted
larsrosenm
New Contributor II

SUM in load script

In SQL I have 1 table, and when i do this SQL i get the result i need. 

"SELECT t1._KeyID,
t1._Tmp_TIME,
t1._TmpID,
sum(q1.FLAG) as xSUM
FROM Table1 t1
LEFT JOIN Table1 t2 on t2._KeyID = t1._KeyID
WHERE (t2._Tmp_TIME between t1._Tmp_TIME and t1._Tmp_END_TIME
AND t1._Tmp_IDX <> t2._Tmp_IDX)
GROUP BY t1._KeyID, t1._Tmp_TIME,t1._TmpID"

 

Table1:

_KeyID_TmpID_Tmp_IDX_Tmp_TIME_Tmp_END_TIMEFLAG
22062019|10026|4114581140409431374608:36:20.000000008:37:20.00000001
22062019|10026|4114581140409441374608:36:21.000000008:37:21.00000001
22062019|10026|4114581140409451374608:36:23.000000008:37:23.00000001
22062019|10026|4114581140409321374608:36:24.000000008:37:24.00000001
22062019|10026|4114581140406431374608:36:26.000000008:37:26.00000001
22062019|10026|4114581140406441374608:36:26.000000008:37:26.00000001
22062019|10026|4114581140406451390008:36:28.000000008:37:28.00000001
22062019|10026|4114581140406461390008:36:30.000000008:37:30.00000001
22062019|10026|4114581140406471357708:36:32.000000008:37:32.00000001
22062019|10026|4114581140406481372608:36:37.000000008:37:37.00000001

 

Result of SQL:

_KeyID_Tmp_TIME_TmpIDxSUM
22062019|10026|41145808:36:20.00000001140409434
22062019|10026|41145808:36:21.00000001140409444
22062019|10026|41145808:36:23.00000001140409454
22062019|10026|41145808:36:24.00000001140409324
22062019|10026|41145808:36:26.00000001140406434
22062019|10026|41145808:36:26.00000001140406444
22062019|10026|41145808:36:28.00000001140406452
22062019|10026|41145808:36:30.00000001140406462

 

The Table1 is easy to enough to create as  a resident table in LOAD script, but my mind is tired and fresh out of ideas to in converting the SQL Select to LOAD SCRIPT.

So how do i do this in the LOAD SCRIPT?

 

Note: The data i'm normally loads comes from QVDs