7 Replies Latest reply: Jun 7, 2016 5:51 AM by Onno van Knotsenburg RSS

    Subquery in script load

    Simone Ginepro

      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

        • Re: Subquery in script load
          Sasidhar Parupudi

          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;

          • Re: Subquery in script load
            Simone Ginepro

            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

            • Re: Subquery in script load
              Simone Ginepro

              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

                • Re: Subquery in script load
                  Sasidhar Parupudi

                  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;