Qlik Community

Qlik Sense App Development

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

Not applicable

SQL SubQuery Equivalent in QlikScript?

Table:

Date,Sales

12-07-2016,3

13-07-2016,9

14-07-2016,1

19-07-2016,10

22-07-2016,2

25-07-2016,0

OutPut:

Date,Sales,Selective_Sum

12-07-2016,3,0

13-07-2016,9,3

14-07-2016,1,12

19-07-2016,10,0

22-07-2016,2,10

25-07-2016,0,2

Logic: Selective_Sum = Sum of Sales in Last Three Days from the current date for each row.

If I were to do this is SQL ,that would be a piece of cake using a subquery.

Note: You are free to order things up.

Note: There are gaps between the dates. Sum of Last three rows is not the solution I am looking for.

Please avoid loops if possible.

Thanks alot in advance.

6 Replies
MVP
MVP

Re: SQL SubQuery Equivalent in QlikScript?

1.png

SET DateFormat='DD-MM-YYYY';

Table:

LOAD * Inline [

Date,Sales

12-07-2016,3

13-07-2016,9

14-07-2016,1

19-07-2016,10

22-07-2016,2

25-07-2016,0

];

Left Join (Table)

LOAD

  Date +1 as Date,

  Sales as Sales_1

Resident Table;

Left Join (Table)

LOAD

  Date +2 as Date,

  Sales as Sales_2

Resident Table;

Left Join (Table)

LOAD

  Date +3 as Date,

  Sales as Sales_3

Resident Table;

Left Join (Table)

LOAD

  Date,

  RangeSum(Sales_1, Sales_2, Sales_3) as Selective_Sum

Resident Table;

Not applicable

Re: SQL SubQuery Equivalent in QlikScript?

Thanks maxgro, but I am looking for a solution implementing a conditional statement, cause if I were to extend it for say last six month, this method 'll break. This example is oversimplified but you have shown a direction that might lead to the correct solution. 

Re: SQL SubQuery Equivalent in QlikScript?

Temp:

LOAD

     Date,

     Sales

FROM

     source

     ;

Result:

LOAD

     Date,

     Sales,

     Rangesum(

          If(Date=Peek('Date',-1)+1,Peek('Sales',-1)),

          If(Date=Peek('Date',-2)+2,Peek('Sales',-2)),

          If(Date=Peek('Date',-3)+3,Peek('Sales',-3))

     ) as Selective_Sum

RESIDENT

     Temp

Order By

     Date

     ;

DROP TABLE Temp;


talk is cheap, supply exceeds demand
Not applicable

Re: SQL SubQuery Equivalent in QlikScript?

Re: SQL SubQuery Equivalent in QlikScript?

Well, you asked for the last three days, not then last six months. If you're not clear about what you want you don't get it. See Calculating rolling n-period totals, averages or other aggregations for an approach that can give you what you now asked for.


talk is cheap, supply exceeds demand
Not applicable

Re: SQL SubQuery Equivalent in QlikScript?

Thx @ for the feedback, I'll def. avoid the same mistake while posting my future question