Subquery in script load

[#1]

I've to make a script in qlick sense that is equal to the query below:

(the purpose is to load all and only the Costomer tha has a sum(Value) positive with all fields defined in Table)

select A.Society,A.Customer, A.Date1, A.Date2, A.Level, A.Value

from

     (

          Table as A

               inner join (select B.Society as Society1, B.Customer as Customer1, Sum(B.Value) as ValuePositive

                    from Table as B

                    group by B.Society, B.Customer having sum(B.Value)>0) as Z

          on A.Society=Z.Society1 and A.Customer=Z.Customer1

     )

where A.Level>.....

and other filter...

My question is how I can implement that subquery...

I've tried with aggregate function but I can't surrogate aggregation...

Data are from a sql server db connected via ODBC.

Many thanks

[sasiparupudi1]

Please try like the following

A:

Load * Inline

[

Society, Customer, Date1, Date2, Level, Value

1,1,2015-01-01,2015-02-01,1,10

1,2,2015-01-01,2015-02-01,2,-30

2,1,2015-01-01,2015-03-01,3,50

2,2,2015-01-01,2015-04-01,4,10

3,1,2015-01-01,2015-05-01,5,-900

3,2,2015-01-01,2015-06-01,1,-10

4,1,2015-01-01,2016-02-01,2,10000

4,2,2015-01-01,2015-12-01,1,10010

];

Inner join(A)

Load *

where ValuePositive>0;

Load  Society , Customer  , Sum(Value) as ValuePositive

Resident A

group by Society,  Customer;

[#3]

Thnaks, but I've not specify that the result must be all records frome the first Table (A) with also the negative value if exist, not only the positive...

thanks

[sasiparupudi1]

Can you be more specific? may be post some example

[#5]

Thanks... I posted an example of what I need:

I have a table:

Key	Customer	Society	Date1	Date2	Value
00001	100	10	01/02/2016	05/06/2016	-1000
00002	100	10	01/03/2016	05/06/2016	1500
00003	100	10	01/01/2016	05/06/2016	300
00004	200	20	01/01/2016	05/06/2016	-2000
00005	200	20	01/01/2016	05/06/2016	500
00006	300	10	01/01/2016	05/06/2016	-1500
00007	300	10	01/01/2016	05/06/2016	-1500
00008	300	10	01/01/2016	05/06/2016	4000
00009	100	30	01/01/2016	05/06/2016	-5000
00010	100	30	01/01/2016	05/06/2016	2000

I want to add another field (Calculate Value) where Calculate value is the same as Value in case that the sum(Value) grouped by Customer, Society is positive, else is 0.

The new table must be like this:

Key	Customer	Society	Date1	Date2	Value	CalculateValue
00001	100	10	01/02/2016	05/06/2016	-1000	-1000
00002	100	10	01/03/2016	05/06/2016	1500	1500
00003	100	10	01/01/2016	05/06/2016	300	300
00004	200	20	01/01/2016	05/06/2016	-2000	0
00005	200	20	01/01/2016	05/06/2016	500	0
00006	300	10	01/01/2016	05/06/2016	-1500	-1500
00007	300	10	01/01/2016	05/06/2016	-1500	-1500
00008	300	10	01/01/2016	05/06/2016	4000	4000
00009	100	30	01/01/2016	05/06/2016	-5000	0
00010	100	30	01/01/2016	05/06/2016	2000	0

thanks a lot

[sasiparupudi1]

Please try

A:

Load * Inline

[

Key,Customer,Society,Date1,Date2,Value

00001,100,10,01/02/2016,05/06/2016,-1000

00002,100,10,01/03/2016,05/06/2016,1500

00003,100,10,01/01/2016,05/06/2016,300

00004,200,20,01/01/2016,05/06/2016,-2000

00005,200,20,01/01/2016,05/06/2016,500

00006,300,10,01/01/2016,05/06/2016,-1500

00007,300,10,01/01/2016,05/06/2016,-1500

00008,300,10,01/01/2016,05/06/2016,4000

00009,100,30,01/01/2016,05/06/2016,-5000

00010,100,30,01/01/2016,05/06/2016,2000

];

Left Join(A)

Load Customer,Society , Sum(Value) as NewValue

Resident A

group by Customer,Society;

finalA:

LOAD

Key,Customer,Society,Date1,Date2,Value,

if(NewValue>0,Value,0) as CalculateValue

resident A;

drop Table A;

[#7]

Thank-you very mutch!!

That is what I need. Correct

[oknotsen]

If your question is now answered, please flag the Correct Answer and possible Helpful Answers.

If not, please let us know what part of this topic you still need help with .