"Group By" using two fields from a single table without using concatenate?

[#1]

Hi Guys,

Please help. I want to sum the columns SavingsA  and Savings B group by PolicyAnniv from the table below:

Name	Address	Birthdate	AgeBeforePolicyAnniv	SavingsA	AgeAfterPolicyAnniv	SavingsB
Benjie	NE	07/12/2010	4	$5	4	$16
Joe	TX	12/8/2013	1	$20	2	$20
Sally	CA	5/5/2009	5	$15	5	$28

Ultimately, I want to have the table:

PolicyAnniv	Savings
1	20
2	20
4	21
5	43

One solution is to use concatenate. But in my case, concatenate just produces the wrong number of records.  Is there any other way to do this using a group by in this single table????

[swuehl]

I think you need to concatenate (creating a single PolicyAnniv field and a single Savings field), then group by.

Why does it produce the wrong number of records.

[#3]

Hi Joel,

Do you have any logical connection between the two tables? If so,would it not be better to join in the values and then group it?

/Hampus

[#4]

Hi swuehl,

Thanks for your time.

I still think concatenate is the easiest solution to this one but still encountering counting errors.  To explain the errors:

Let's call:

> The top most table (Policy Table)  = Table A

> The results table (bottom table) = Table C

> The concatenated table A = Table B

Table B looks like this:

POL_NUM      Address            Birthdate       Age      Savings

Benjie               NE               07/12/2010       4          $5

Benjie               NE               07/12/2010       4          $16

Joe                    TX               12/8/2013         1          $20

Joe                    TX               12/8/2013         2          $20

Sally                  CA               5/5/2009          5         $15

Sally                  CA               5/5/2009          5         $28

Say for example,

I want to count policies with Address = "TX" in Table A then my table will display the count = 1.

But if I want to count policies with Address = "TX" in Table B then Qlikview still displays count = 1.

      Isn't Qlikview supposed to display a count of 2 in Table B since we've concatenated the table?

Is there anyway to tell Qlikview to use Table B only?

Sorry, I'm new to Qlikview and I still don't fully understood how Qlikview does these concatenate/joins.

[#5]

Hi Hampus,

Yes, there is a connection between the two tables but the concatenation/join confuses me still.  I have provided an example in my reply above.

Thanks for your time. 😃

[swuehl]

At the time you do the counts, are all three tables present in your data model?

I think you need to decide, if you want to keep table A, having table B only as a temporary table to calculate table C, then drop it.

Or to keep table B permanently,and drop table A.

How does you table view look like right now?

[#7]

Correct, at the time I do the counts, all three tables are present in the data model.

Suppose I opt for:

     Keeping Table A and having Table B as a temporary table then calculating C, then dropping Table B.

Is there anyway to tell Qlikview to use Table A for the counts summary and Table C for the Age - Savings summary?

Sorry, can't really comment on the table view, it's really a mess.

[swuehl]

If you create table C as requested in your original post, there will be no link to table A.

But you can access the data in the data islands of course.

But I don't think you want the tables isolated. To help you more, you need to think and define what you want to analyze.

Some data models support certain kind of analysis better than other, especially if you need to decide if you want your before / after data separated into fields / columns or have separate rows with an additional field as qualifier.

[#9]

I agree, I wouldn't want to isolate the tables as much as possible. 

Thanks for your time swuehl.  I'll just try your suggestion for now and post again the results.  Thanks again. 😃