topic help in outer join in QlikView

help in outer join

Fri, 24 May 2013 10:58:27 GMT

Hi there,

I have a table that contains the customerid, the monthyear combination and the number of transactions that he performed

customer id	monthyear	number of transactions
1	June-2011	1
1	January-2012	3
1	January-2013	5
1	February-2012	3
1	May-2013	7
2	June-2012	4
2	August-2012	6
2	May-2013	9
3	August-2012	43
3	April-2013	21
3	May-2013	34

and i want to get a list with the customerid and all the monthyear combinations starting from June 2011 along with their number of transactions, with the value of '0' where the customer did not perform any.

Thank you.

Re: help in outer join

jvitantonio — Fri, 24 May 2013 11:02:49 GMT

table:

load * from yourTable;

table1:

outer join (table)

load * Resident table;

Drop table table;

Re: help in outer join

Fri, 24 May 2013 11:14:53 GMT

Hi there,

when i run your code it results in no tables, because of the drop table command.

Without the drop table command it just returns the original table.

To be more precise, i want to have 23 records for each customer , one for each month starting June-2011 untill May-2013. If the customer has performed transactions in one of these months it should have that value in the "number of transactions" field.

Other wise it should be equal to zero.

I also have a table with all the month year combinations starting from June-2011 untill May-2013.

Thank you.

Re: help in outer join

Fri, 24 May 2013 11:38:22 GMT

Hi,

You can try this,

Load ..,

Monthyear,

From Calendar;

JOIN(Monthyear)

Load ..,

Monthyear,

From Transactions;

Thanks,

Re: help in outer join

jvitantonio — Fri, 24 May 2013 11:50:02 GMT

Hi, sorry but why 23? If you have 3 clients and 8 distinct Month-Year, that would be 24. Is this what you want?

Re: help in outer join

Wed, 29 May 2013 07:50:01 GMT

Hi there,

Unfortunately this does not work for me.

if for customerid =1 i have the following data:

customer id	monthyear	number of transactions
1	June-2011	1
1	January-2012	3
1	January-2013	5
1	February-2012	3
1	May-2013	7

the result i want to achieve should be:

customer id	monthyear	number of transactions
1	June-2011	1
1	July-2011	0
1	August-2011	0
1	September-2011	0
1	October-2011	0
1	November-2011	0
1	December-2011	0
1	January-2012	3
1	February-2012	3
1	March-2012	0
1	April-2012	0
1	May-2012	0
1	June-2012	0
1	July-2012	0
1	August-2012	0
1	September-2012	0
1	October-2012	0
1	November-2012	0
1	December-2012	0
1	January-2013	5
1	February-2013	0
1	March-2013	0
1	April-2013	0
1	May-2013	7

Thank you.