5 Replies Latest reply: Dec 4, 2012 1:31 PM by Wojciech Parzyszek RSS

    Subtotal Expression after Concatenate,Inner and Load Functs

    Jaime Marines
      
      

       

      Hello QlikCommunity;

       

      I am working with below expressions:

       

      Directory;
      TablaA:
      LOAD
      //,'Table 1' as Table,
          REMISION as remision,
          AEROPUERTO as idestacion,
          LITROS as vnatural,
          CONCEPTO as idproducto,
          Year,
          Month,
          Day
      FROM
      [C:\Users\jmarinesc\Downloads\SIAMCO\01_DATA\SQL\QVD\PedidosVentas1Jun2010.qvd](qvd);

      CONCATENATE

      //***************MOVIMIENTO TABLE******************

      LOAD
      //,'Table 2' as Table,
          idmovimiento,
          tmovimiento,
          mtipo,
          remision,
          idestacion,
          vnatural,
          idproducto,
          Year,
          Month,
          Day
      FROM
      [C:\Users\jmarinesc\Downloads\SIAMCO\01_DATA\SQL\QVD\Movs1Jun2010.qvd](qvd);

       

      //******************INNER JOIN*************************
      INNER JOIN (TablaA)
      LOAD*
      WHERE "NoMatch"
        OR  "SiMatch"
        OR "Duplicadas"
        OR  "DifEstacion"
        OR  "DifLitros"
        OR "IgDesc"
        OR "NoMatch1"
        OR "NoMatch2"
      ;
      LOAD
      remision
      ,if(count(remision)=1 ,-1)as "NoMatch"
      ,if(count(remision)>=2,-2)as "SiMatch"
      ,if(count(remision)>=2,-3) as "Duplicadas"
      ,if(maxstring(idestacion)<>minstring(idestacion),-4) as "DifEstacion"
      ,if(max(vnatural)<>min(vnatural),-5)as "DifLitros"
      ,if(maxstring(idproducto)= minstring(idproducto),-7)as "IgDesc"
      ,if(count(remision)>=2 and (maxstring(idproducto)=minstring(idproducto)),-1) as "NoMatch1"
      ,if(count(remision)>=2 and (maxstring(idproducto)<>minstring(idproducto))and (maxstring(tmovimiento)=minstring(tmovimiento))and (maxstring(mtipo)=minstring(mtipo))and (maxstring(idmovimiento)<>minstring(idmovimiento)),-1) as "NoMatch2"

      RESIDENT TablaA
      GROUP BY remision;

      STORE TablaA INTO C:\Users\jmarinesc\Downloads\SIAMCO\01_DATA\SQL\QVD\ESLK1JJOINA.qvd; 

       

      Then;

       

      I need to know how many equal ESremision exists making a subtotal on ESNOMATCH2 field and create new field Subtotal, with Subt results (4).

       

      ESremisionESNOMATCH2Subtotal
      1520689-14
      1520689-14
      1520689-14
      1520689-14

       

      Like An Excel Subt Function.........

       

      RemisionTot
      11
      Cuenta 11
      2221
      2221
      Cuenta 2222
      3331
      3331
      3331
      Cuenta 3333
      44441
      44441
      44441
      44441
      Cuenta 44444
      555551
      555551
      555551
      555551
      555551
      Cuenta 555555
      Cuenta general15

       

      Tk in advanced

       

      JMC

        • Re: Subtotal Expression after Concatenate,Inner and Load Functs

          add at the end something like:

           

          left join load

          distinc ESremision,

          count(ESremision) as Subtotal

          group by ESremision;

            • Re: Subtotal Expression after Concatenate,Inner and Load Functs
              Jaime Marines

              Hello Pari Pari;

               

              It works partially, because I lost my concatenated table.....

                  • Re: Subtotal Expression after Concatenate,Inner and Load Functs
                    Jaime Marines

                    Hello!

                     

                    I don´t get error.....

                     

                    the result of this is:

                     

                     

                    TablaA << PedidosVentas1Jun2010 (qvd optimized) 20,292 registros leídos
                    TablaA << Movs1Jun2010 (qvd optimized) 46,066 registros leídos
                    TablaA 26,031 registros leídos

                    --- Ejecución de Script finalizada ---

                     

                    • But, I don´t find  Subtotal field......

                     

                    SET ThousandSep=',';
                    SET DecimalSep='.';
                    SET MoneyThousandSep=',';
                    SET MoneyDecimalSep='.';
                    SET MoneyFormat='$#,##0.00;-$#,##0.00';
                    SET TimeFormat='hh:mm:ss TT';
                    SET DateFormat='DD/MM/YYYY';
                    SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff] TT';
                    SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';
                    SET DayNames='lun;mar;mié;jue;vie;sáb;dom';

                    Directory;
                    TablaA:
                    LOAD

                     

                        REMISION as remision,
                        AEROPUERTO as idestacion,
                        LITROS as vnatural,
                        CONCEPTO as idproducto,
                        Year,
                        Month,
                        Day
                    FROM
                    [C:\Users\jmarinesc\Downloads\SIAMCO\01_DATA\SQL\QVD\PedidosVentas1Jun2010.qvd](qvd);

                    CONCATENATE

                    //***************MOVIMIENTO TABLE******************

                    LOAD
                        idmovimiento,
                        tmovimiento,
                        mtipo,
                        remision,
                        idestacion,
                        vnatural,
                        idproducto,
                        Year,
                        Month,
                        Day
                    FROM
                    [C:\Users\jmarinesc\Downloads\SIAMCO\01_DATA\SQL\QVD\Movs1Jun2010.qvd](qvd);

                    //******************INNER JOIN*************************
                    INNER JOIN (TablaA)
                    LOAD*
                    WHERE "NoMatch"
                      OR  "SiMatch"
                      OR "Dup"
                      OR "Trip"
                      OR "Cuad"
                      OR "Quint"
                      OR ">5"
                      OR  "DifEstacion"
                      OR  "DifLitros"
                      OR "IgDesc"
                      OR "NoMatch1"
                      OR "NoMatch2"
                    ;
                    LOAD
                    remision
                    ,if(count(remision)=1 ,-1)as "NoMatch"
                    ,if(count(remision)>=2,-2)as "SiMatch"
                    ,if(maxstring(idestacion)<>minstring(idestacion),-4) as "DifEstacion"
                    ,if(max(vnatural)<>min(vnatural),-5)as "DifLitros"
                    ,if(maxstring(idproducto)= minstring(idproducto),-7)as "IgDesc"
                    ,if(count(remision)>=2 and (maxstring(idproducto)=minstring(idproducto)),-1) as "NoMatch1"
                    ,if(count(remision)>=2 and (count(distinct tmovimiento))=1  and (sum(idproducto))<=10,-1)as "NoMatch2"
                    ,if(count(remision)=2,-12) as "Dup"
                    ,if(count(remision)=3,-13) as "Trip"
                    ,if(count(remision)=4,-14) as "Cuad"
                    ,if(count(remision)=5,-15) as "Quint"
                    ,if(count(remision)>5,-16) as ">5"

                    RESIDENT TablaA
                    group by remision;

                     


                    left join LOAD
                    distinct remision,

                    count(remision)as Subtotal

                    group by remision;

                    STORE TablaA INTO C:\Users\jmarinesc\Downloads\SIAMCO\01_DATA\SQL\QVD\ESLK1JJOINA.qvd; 

                     

                    Tk in advanced!