5 Replies Latest reply: Feb 8, 2012 3:09 PM by Brian Vanderbusch RSS

    Create Column based on sum of totals in resident table

      Hi everyone, I'm a newbie to Qlikview, so this might be a simple question for most of you, but I searched and couldn't find what I needed so:

       

      I have a T1 app that need to store 2 tables.  The first is a basic load from an excel file.  However the second table needs to be created entirely from calculations based on the first table, and I don't know how to do this.  An example of the first table would be:

       

      DateStateStepSum
      1/1/2011ALStart50
      1/1/2011ALComplete40
      1/1/2011ALPurchase30
      1/1/2011CAStart50
      1/1/2011CAComplete40
      1/1/2011CAPurchase30
      1/2/2011ALStart50
      1/2/2011ALComplete50
      1/2/2011ALPurchase50




       

      In actuallity, there are far more days, all 50 states, and many more "steps".  What I need to do is create a table with 4 new columns, based on either the sum of the "Sum" column unique to each state-step combination, or create a column in that new table based on the column I just described.

       

      Can anyone help get me started here?  I've been experimenting with FOR EACH, figuring I'd load and concantenate to the new table within each iteration, but I can't figure out the syntax.  Is this even the right way to go about this?

       

      Thank you!

        • Create Column based on sum of totals in resident table
          Stephen Redmond

          Hi,

           

          You probably want to use resident loads.  You might also want to create a Composite key of State and Step so as to avoid a Synthetic key.

           

          e.g:

           

          DataTable:

          Load

               Date,

               State,

               Step,

               State & '-' & Step as StateStepKey,

               // or you could use AutoNumberHash256(State, Step) As StateStepKey, which creates a numeric key    

               Sum

          From myexcelfile...;

           

          SummaryTable:

          Load

               StateStepKey,

               Sum_of_Sum,

               Sum_of_Sum / Count_of_Key as Avg_Sum_Value;

          Load

               StateStepKey,

               Sum(Sum) As Sum_of_Sum,

               Count(StateStepKey) As Count_of_Key

          Resident

               DataTable

          Group by StateStepKey;

           

          This uses a preceding load to do a calculation with the newly derived Sum_of_Sum - I don't know exactly what you want to do with that but I am showing an example.

           

           

           

          Regards,

           

           


          Stephen