Simple load question

[#1]

Hello,

I am still waiting for my organization to get my qlikview training approved.

So I am  relaying on self learning and  community support.

Here is what I am trying to do

First I am loading customer table

Customer_ID	Name
001	Mat
002	Jim
003	Sam
004	Peter
005	Jil

Then the sales table

Order_ID	Customer_ID	Year	SalesAmt
001	001	2009	15
002	001	2009	10
003	002	2009	10
004	003	2009	50
005	004	2009	45
006	003	2010	25
007	004	2010	5
009	001	2011	100
010	002	2011	75
011	002	2011	20
012	003	2011	30

Then I am trying to create an aggregate table as shown below

Customer_ID	Name	2009_SALES	2010_SALES	2011_SALES
001	Mat	25	0	100
002	Jim	10	0	95
003	Sam	50	25	30
004	Peter	45	5	0
005	Jil	0	0	0

The challenge I am facing is programmatically add 0 for the missingcustomers . In the above example Customer_ID = "005"

Customer_ID	2009_SALES	2010_SALES	2011_SALES
001	25	0	100
002	10	0	95
003	50	25	30
004	45	5	0
005

here is the script I am using

[Customer]:

LOAD * INLINE [

Customer_ID,Name

001,Mat

002,Jim

003,Sam

004,Peter

005,Jil

];

[Sales]:

LOAD * INLINE [

Order_ID,Customer_ID,Year,AMT

001,001,2009,15

002,001,2009,10

003,002,2009,10

004,003,2009,50

005,004,2009,45

006,003,2010,25

007,004,2010,5

009,001,2011,100

010,002,2011,75

011,002,2011,20

012,003,2011,30

];

[AGG_TAB]:

load

Customer_ID,

sum(if(Year = 2009, AMT,0)) as '2009_SALES',

sum(if(Year = 2010, AMT,0)) as '2010_SALES',

sum(if(Year = 2011, AMT,0)) as '2011_SALES'

Resident Sales

group by Customer_ID

;

Would some one educate me how to get the Customer_id 005 to AGG_TAB

Thanks,

Aji Paul.

[pover]

Aji,

Self-learning is the best way to learn.  I wouldn't create the Aggr_table since this should be done on the GUI side and this is what makes QV so great.  Just load the Customer and Sales tables.  Then create a pivot table in the GUI that hase Customer and Year as dimensions and sum(Sales) as the expression.  In the presentation tab, remove the option to "Suppress Zero Values" which will allow you to see customers without sales.  You will also have to drag and drop the year dimension column above the expression to get the cross table view you are looking for.

Karl

[#3]

Karl,

Thanks for the quick reply!

I would like to have the aggregate table for certain reasons.

1. Need to find out who did not buy a product in certain year or never bought anything.

2. Would like to add a yearly Sales amount BIN . ex, "Nothing Purchased" , "only free products", "$1-$100", "$101-$1000" and so forth. 

Thanks again,

Aji.

[pover · Accepted solution]

Aji,

Try doing a left join between the Customer and Sales before doing the aggregation.  After the agg table you might have to load the table again to replace the null values with zeros so that the bin works.

Karl

[#5]

Karl,

Thanks again!

BTW is NULL is not supported by QV right?

Is there any alternate to that?

Thanks,

Aji.

[pover]

Aji,

It would be

if(isnull(2009_Sales), 0, 2009_Sales) as 2009_Sales

Karl

[#7]

Thanks Karl! That worked. The only addition that I did was,first loaded a Year table with possible years. Then had the joins and worked flawlessly