topic Table reduction using distinct- help? in QlikView

Table reduction using distinct- help?

Tue, 22 Dec 2009 18:08:27 GMT

Hi,

I am pretty sure I am doing something incorrectly in script here but can't work out what. I am trying to do a resident load from one table, summing values from a previous table.

I want to change this:

CustomerID	Product	Month	C1	C2	C3	C4	C5
A	1	jan.09	100	0	0	0	0
A	1	jan.09	0	200	0	0	0
A	1	jan.09	0	0	300	0	0

into this:

CustomerID	Product	Month	C1	C2	C3	C4	C5
A	1	jan.09	100	200	300	0	0

The code I am using is:

BUSINESSNEW4:

LOAD

DISTINCT

CUSTOMERID

PRODUCT,

MONTH,

SUM(C1) AS C1,

SUM

(C2) AS C2,

SUM

(C3) AS C3,

SUM

(C4) AS C4,

SUM

(C5) AS C5

RESIDENT

GROUP

DROP

TABLE

BUSINESSNEW3;

BY CUSTOMERID, PRODUCT, MONTH;BUSINESSNEW3

The script execution progress states the following:

BUSINESSNEW3 << BUSINESSNEW2 37,596 lines fetched

BUSINESSNEW3 << BUSINESSNEW3 73,343 lines fetched

It is almost doubling the number of lines and not recognising the name BUSINESSNEW4.

I can't see where the problem is. Any ideas?

Thanks

Table reduction using distinct- help?

Tue, 22 Dec 2009 18:44:39 GMT

Hi,

here are an working example of your data. Hope it helps to find out the difference.

Merry Christmas!

Rainer

Table reduction using distinct- help?

vidyut — Tue, 22 Dec 2009 18:45:55 GMT

Add one more field

Select 1 as TmpID, .....

In the New Table, and Load again. Should work.

Table reduction using distinct- help?

Tue, 22 Dec 2009 18:58:39 GMT

Thanks Rainer.

I did not realise that you can not give a calculated field the same name as the field it is calculating from, but your example works great!

Merry Christmas to you too.

Table reduction using distinct- help?

Tue, 22 Dec 2009 19:00:23 GMT

Vidyut, same reply to you. Your solution works as well.

Many thanks for your help.