The issue on left join

[#1]

Hi All,

How to handle this issue:

I have 2 tables:

A: The whole memberlist

MemberId,Date

1, 2010-1-1

2, 2010-2-2

3, 2010-3-3

B: The buyer records

MemberId, Date, Volume

1, 2010-1-1,5

2, 2010-2-2,1

I want to join that two table into one and show all the transaction records.

I use A left join B

But the results are:

MemberId, Date, Volume

1, 2010-1-1,5

2, 2010-2-2,1

3, 2010-3-3,-

Please note the record 3, the volume is null, I want it to be 0 not '-', how to solve it?

Thanks.

[jagan]

Hi,

Use this script

TempTable:

LOAD

     MemberId,

     Date

FROM DataSource1;

LEFT JOIN(TempTable)

LOAD

     MemberId,

     Date,

     Volume

FROM DataSource2;

TableName:

LOAD

     MemberId,

     Date,

     Alt(Volume, 0) AS Volume

FROM TempTable;

DROP TABLE TempTable;

Regards,

Jagan.

[#3]

Hi jagan,

Thanks for you quick reply, I know you solution works well, however, if the TempTable is very large, I think it is an expensive consumer, is there any other choice?

Thanks.

[jagan]

Hi,

You actually know whether Volume is Null only after joining the two tables, so if you want that in script you have to implement this logic.  Otherwise you can manage this by using Alt(Volume, 0) wherever you are using this field as dimension / expression.

Hope this helps you.

Regards,

Jagan.