topic SQL SubQuery Equivalent in QlikScript? in App Development

SQL SubQuery Equivalent in QlikScript?

Sun, 24 Jul 2016 19:25:46 GMT

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.

Re: SQL SubQuery Equivalent in QlikScript?

maxgro — Sun, 24 Jul 2016 21:32:32 GMT

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;

Re: SQL SubQuery Equivalent in QlikScript?

Mon, 25 Jul 2016 02:31:45 GMT

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?

Gysbert_Wassenaar — Mon, 25 Jul 2016 07:07:43 GMT

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;

Re: SQL SubQuery Equivalent in QlikScript?

Mon, 25 Jul 2016 07:31:13 GMT

Thx gwassenaar

Re: SQL SubQuery Equivalent in QlikScript?

Gysbert_Wassenaar — Mon, 25 Jul 2016 07:41:55 GMT

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.

Re: SQL SubQuery Equivalent in QlikScript?

Mon, 25 Jul 2016 09:48:50 GMT

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