4 Replies Latest reply: May 17, 2011 6:15 AM by awbconsulting RSS

    error in script with if or set analysis

      Hi Friends,

      i have a problem with a formula.

       

      If I use

      sum({$<OC = {'Z100'}, SM = {'10'} , Linea_P -={'35'}>} F_Imp)

      in a field of table it's run

       

      but if I use the formula:

      LOAD * ,

      sum({$<OC = {'Z100'}, SM = {'10'} , Linea_P -={'35'}>} F_K3) as F_Imp_Avv

      FROM $(vQVDDir)\COPA.QVD

      (qvd);

       

      or

       

      LOAD * ,

      {$<OC = {'Z100'}, SM = {'10'} , Linea_P -={'35'}>} F_K3 as F_Imp_Avv

      FROM $(vQVDDir)\COPA.QVD

      (qvd);

       

       

      in the script it don't work.

       

      I Tried with:

      sum(if (OC = 'Z100' and SM = '10' and Linea_P <> '35', F_Imp ))

      but don't work too.

       

       

      Can you help me??

      thanks a lot

        • error in script with if or set analysis

          Hey you would need to do the group by.

           

          Something like below:

           

          Cheers.

           

          Test:

          LOAD * INLINE [

              Country, City, Sales, Year

              Australia, Melbourne, 500, 2008

              Canada, Toronto, 900, 2008

              India, Mumbai, 1600, 2008

              India, Chennai, 2500, 2008

              Australia, Melbourne, 700, 2009

              Canada, Toronto, 800, 2009

              India, Mumbai, 2000, 2009

              India, Chennai, 3000, 2009

          ];

           

           

          Test2:

          load

              sum(if(Year<>2008 and City='Mumbai',Sales)) as NewSales,

              sum(if(Year=2009 and City<>'Chennai'and City <>'Mumbai',Sales)) as NewSales2

              Resident Test

              group by Country,City;

          • error in script with if or set analysis
            Angus Monro

            Hi,

             

            my reading of the Reference Manual is that the script language doesn't actually support set expressions - these are only discussed in the RM in the context of chart expressions.  This issue threw me just last week, too!  Hence, in the script you'll need to take advantage of the if() function and the WHERE clause in your load statements.

             

            Angus.

            • error in script with if or set analysis

              Set analysis don't work in Script.

              sum(if (OC = 'Z100' and SM = '10' and Linea_P <> '35', F_Imp ))

              group by <all the fields u r using in the table.>

               

              This should work correctly.

              May be u r missing "group by " clause while loading the table.