5 Replies Latest reply: Jun 2, 2010 4:03 AM by EvgenyGin_J RSS

    How to do a group by with expressions?

    EvgenyGin_J

      here is query, how do you thing, wich expression tools it could be done

      select count(distinct idGoods) as cnt, idDoc from drSales where idDoc in (select id from docJournal where opdate >= '2008-10-10' and opDate < '2008-10-11') group by idDoc

      ho to do a group by in expression where is subquery

       

      thanks

       

        • How to do a group by with expressions?

          There is nothing wrong with the SQL statement that you've entered here, that I can see.

          What exactly is your question?

          • How to do a group by with expressions?
            Saravana Prabhu Paramaramaraj

             

             

             

             



             

             

             

            Following is the code that I've tried in my hands-on :

             


            load
            `Employee ID`,
            `Database` as [Database5],
            sum(Experience) as Db_Exp
            resident
            Table1 where Database<>'-' group by `Employee ID`, `Database`;


            In my view, there is no problem with query, check your data and fields.



              • How to do a group by with expressions?
                EvgenyGin_J

                I mean, that i'm loading 5 tables with 100000 to 10 000 000 rows in each table.

                if i will load new data by this sql statement - it would be double data in cash. That's why - I have got info from 5 tables by first loading and thhan i guess i should manipulate this data by tools in qlikview - like expression, but not to execute another pne query with joins. Am i right or not?

                  • How to do a group by with expressions?
                    John Witherspoon

                    Not sure if I'm following. If you're saying that, for instance, you've already got a load of docJournal, then yes, it would be inefficient to repeat that load here.

                    The normal approach would be to create QVDs for your two tables. Then you would do the load from the QVDs. Something like this (untested):

                    [Table1]:
                    LOAD * INLINE [
                    opDate
                    2008-10-10
                    2008-10-11
                    ];
                    INNER JOIN ([Table1])
                    LOAD
                    "id" as "idDoc"
                    ,"opDate"
                    FROM docJournal.qvd (QVD)
                    WHERE exists("opDate") // done as exists instead of date check for optimized load
                    ;
                    [Table2]:
                    LOAD
                    "idDoc"
                    ,count(distinct idGoods) as cnt
                    FROM drSales.qvd (QVD)
                    WHERE exists("idDoc")
                    ;
                    DROP TABLE [Table1];