3 Replies Latest reply: Sep 7, 2011 1:24 PM by Jason Michaelides RSS

    GroupBy week

    sandeepa rout

      Hi All,

       

      I am using Qlikview10.

       

      I have two tables Header and Detail.

       

      Header contains : CustCode, InvoiceNo, InvoiceDate

       

      Detail Contains: ProdCode, Qty, Value, Type(D/C),InvoiceNo

       

      I have to make a report which contains All of these value and should be arranged BY WEEK.

       

      Please Suggest How To Achieve It?

       

      Thnx,

      Sandeepa

        • Re: GroupBy week
          s j

          Invoice_Details:

          Load

           

             InvoiceNo,

             Qty,

             Value

           

          From Detail_Table;

           

          LEFT JOIN

           

          Load

             Weekname(InvoiceDate) as Inv_Week,

             InvoiceNo

          From Header_Table;

           

          Weekly_Data:

          Load

               Inv_Week,

               Sum(Qty) as Week_Qty,

               Sum(Value) as Week_Value

          Resident

          Invoice_Details Group by Inv_Week;

          • GroupBy week
            Anand Chouhan

            Hi,

             

            Use Order by clause to group the data

             

            like

             

            Header:

            Load

            CustCode,

            InvoiceNo,

            InvoiceDate

            From Header

             

            Join(Header)

            Load

            ProdCode,

            Qty,

            Value,

            Type(D/C),

            InvoiceNo

            From Detail

             

            Load

            *,

            Week(InvoiceDate) as Week

            Resident Header

            Order By Week(InvoiceDate);

             

             

            Regards,

            Anand

              • GroupBy week
                Jason Michaelides

                If the data volumes are very large (10s of millions) then I'd join the tables. Otherwise just load the tables separately as normal but add the following to the Header load:

                 

                Week(Invoice_Date) as Invoice_Week

                 

                Then add the field Invoice_Week as a dimension in your chart.

                 

                Jason.