Aggregating while connecting

[#1]

I want to connect two tables. The first one contains one row for each client and has the following:

ID    Name

01    ABC

02    DEF

03    GHI

04    JKL

In the second table I have payments by payment date as follows:

Month     ID     Amount

Jan         01       500

Feb        01        123

Feb        02        256

Mar        04        589

Jan         04        563

In actual data I have 17 million records in the second table and it connects to around 5,000 records in the first table. While QV does the connection well it takes time and space to reload data.

Since the first table has only one row for each client is it possible to aggregate the information in the second table by ID. I do not need the Month for this purpose.

Would appreciate guidance in this matter.

Thanks & regards

[maxgro]

yes it possible

C:

load * inline [

ID ,   Name

01 ,   ABC

02 ,   DEF

03 ,   GHI

04 ,   JKL

];

left join (C)

load ID, sum(Amount) as Amount group by ID;

load * inline [

Month  ,   ID ,    Amount

Jan    ,     01  ,     500

Feb    ,    01   ,     123

Feb    ,    02   ,     256

Mar    ,    04   ,     589

Jan    ,     04  ,      563

];

or if  you prefere 2 tables

C:

load * inline [

ID ,   Name

01 ,   ABC

02 ,   DEF

03 ,   GHI

04 ,   JKL

];

P:

load ID, sum(Amount) as Amount group by ID;

load * inline [

Month  ,   ID ,    Amount

Jan    ,     01  ,     500

Feb    ,    01   ,     123

Feb    ,    02   ,     256

Mar    ,    04   ,     589

Jan    ,     04  ,      563

];

and a very good doc for loading data in Qlikview

LOAD data into QlikView

[its_anandrjs]

Hi,

You can do this like

Temp:

LOAD * Inline

[

ID,    Name

01,    ABC

02,    DEF

03,    GHI

04,   JKL

];

Left Join

load * Inline

[

Month,     ID,     Amount

Jan,        01,       500

Feb,        01,        123

Feb,        02,        256

Mar,        04,        589

Jan,         04,       563

];

Left Join

load

ID,

sum(Amount) as AmountValue

Resident Temp

Group By ID;

DROP Field Month;

Hope this helps

Thanks & Regards

[its_anandrjs]

Hi,

One of the other way to do this also

Temp:

load ID &'|'& Name as Key,ID as ID, Name as Name ;

LOAD * Inline

[

ID,    Name

01,    ABC

02,    DEF

03,    GHI

04,   JKL

];

Left Join

load * Inline

[

Month,ID,Amount

Jan,01,500

Feb,01,123

Feb,02,256

Mar,04,589

Jan,04,563

];

Left Join

Data:

load

Key,

sum(Amount) as AmtSum

Resident Temp

Group by Key;

EXIT Script;

Hope this helps

Thanks & Regards