topic Re: Aggregating while connecting in QlikView

Aggregating while connecting

Thu, 20 Mar 2014 06:37:13 GMT

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

Re: Aggregating while connecting

maxgro — Thu, 20 Mar 2014 06:47:47 GMT

yes it possible

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

load * inline [

ID , Name

01 , ABC

02 , DEF

03 , GHI

04 , JKL

];

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

Re: Aggregating while connecting

its_anandrjs — Thu, 20 Mar 2014 06:56:02 GMT

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

Re: Aggregating while connecting

its_anandrjs — Thu, 20 Mar 2014 07:18:34 GMT

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