Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Subquery in script load

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

7 Replies
sasiparupudi1
Honored Contributor III

Re: Subquery in script load

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;

Not applicable

Re: Subquery in script load

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
Honored Contributor III

Re: Subquery in script load

Can you be more specific? may be post some example

Not applicable

Re: Subquery in script load

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

I have a table:

   

KeyCustomerSocietyDate1Date2Value
000011001001/02/201605/06/2016-1000
000021001001/03/201605/06/20161500
000031001001/01/201605/06/2016300
000042002001/01/201605/06/2016-2000
000052002001/01/201605/06/2016500
000063001001/01/201605/06/2016-1500
000073001001/01/201605/06/2016-1500
000083001001/01/201605/06/20164000
000091003001/01/201605/06/2016-5000
000101003001/01/201605/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:

    

KeyCustomerSocietyDate1Date2ValueCalculateValue
000011001001/02/201605/06/2016-1000-1000
000021001001/03/201605/06/201615001500
000031001001/01/201605/06/2016300300
000042002001/01/201605/06/2016-20000
000052002001/01/201605/06/20165000
000063001001/01/201605/06/2016-1500-1500
000073001001/01/201605/06/2016-1500-1500
000083001001/01/201605/06/201640004000
000091003001/01/201605/06/2016-50000
000101003001/01/201605/06/201620000

thanks a lot

sasiparupudi1
Honored Contributor III

Re: Subquery in script load

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;

Not applicable

Re: Subquery in script load

Thank-you very mutch!!

That is what I need. Correct

oknotsen
Honored Contributor III

Re: Subquery in script load

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 .

May you live in interesting times!