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

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

Wed, 10 Jun 2015 07:16:09 GMT

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????

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

swuehl — Wed, 10 Jun 2015 08:39:15 GMT

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.

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

Wed, 10 Jun 2015 08:52:55 GMT

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

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

Wed, 10 Jun 2015 09:31:32 GMT

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.

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

Wed, 10 Jun 2015 09:32:46 GMT

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. 😃

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

swuehl — Wed, 10 Jun 2015 09:38:48 GMT

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?

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

Wed, 10 Jun 2015 09:51:00 GMT

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.

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

swuehl — Wed, 10 Jun 2015 09:57:32 GMT

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.

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

Wed, 10 Jun 2015 10:31:45 GMT

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. 😃