Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

help in outer join

Hi there,

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

customer idmonthyearnumber of transactions
1June-20111
1January-20123
1January-20135
1February-20123
1May-20137
2June-20124
2August-20126
2May-20139
3August-201243
3April-201321
3May-201334

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.

Tags (2)
5 Replies
Luminary
Luminary

Re: help in outer join

table:

load * from yourTable;

table1:

outer join (table)

load * Resident table;

Drop table table;

Not applicable

Re: help in outer join

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.

Not applicable

Re: help in outer join

Hi,

You can try this,

A:

Load ..,

        Monthyear,

        ..

From Calendar;

JOIN(Monthyear)

Load ..,

        Monthyear,

        ..

From Transactions;

Thanks,

Luminary
Luminary

Re: help in outer join

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

Not applicable

Re: help in outer join

Hi there,

Unfortunately this does not work for me.

if for customerid =1 i have the following data:

customer idmonthyearnumber of transactions
1June-20111
1January-20123
1January-20135
1February-20123
1May-20137

the result i want to achieve should be:

customer idmonthyearnumber of transactions
1June-20111
1July-20110
1August-20110
1September-20110
1October-20110
1November-20110
1December-20110
1January-20123
1February-20123
1March-20120
1April-20120
1May-20120
1June-20120
1July-20120
1August-20120
1September-20120
1October-20120
1November-20120
1December-20120
1January-20135
1February-20130
1March-20130
1April-20130
1May-20137

Thank you.