Create Column based on sum of totals in resident table

[#1]

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:

Date	State	Step	Sum
1/1/2011	AL	Start	50
1/1/2011	AL	Complete	40
1/1/2011	AL	Purchase	30
1/1/2011	CA	Start	50
1/1/2011	CA	Complete	40
1/1/2011	CA	Purchase	30
1/2/2011	AL	Start	50
1/2/2011	AL	Complete	50
1/2/2011	AL	Purchase	50

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!

[stephencredmond]

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

[#3]

I think you might have the wrong idea?  Imagine this as creating a pivot table from the original table.  It would need to have a total column, a state column, and a step column, and the total column would have a value that sums the values for each day, the associated data points from the State-Step composite you mentioned. 

Like I said, I'm a newbie to Qlikview, so maybe this is pretty basic. I'm qualifying both tables where necesarry, so syn tables/key aren't an issue, and it would be necesarry to maintain the states and steps in individual columns.  I'm going to mess around with the Group by, but I still think this would have to be in a For Each construct, but I don't know how to reference a table in the IN portion of this syntax.

I could create an array of states but... this proprietary language is is limited and confusing.

[Anonymous]

Brian,

From what I see in your description, there is no need to do anything in the script.  Simply create a chart (pivot table) with dimensions State and Step, and expression sum(Sum).  Drag the Step dimension to the top.
That's probably it, if I got your requirements right.

Regards,
Michael

[#5]

This has to be a second table in the data model, due to the next step calculations that need to be run on this secondary dataset

Sent from my Verizon Wireless smartphone

[Anonymous]

In this case:

Table2:
LOAD
sum(Sum) as TotalSum,
State,
Step
RESIDENT
GROUP BY State, Step;

To keep both source table and Table2 and avoid synthetic keys - use Stephen's solution, which is essentialy the same.