Qlik Community

Qlik Sense App Development

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

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