Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
maxgro
MVP
MVP

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
Author

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. 

Gysbert_Wassenaar

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
Author

Gysbert_Wassenaar

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
Author

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