topic The issue on left join in QlikView

The issue on left join

Mon, 16 Apr 2012 09:32:15 GMT

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.

Re: The issue on left join

jagan — Mon, 16 Apr 2012 09:51:42 GMT

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.

The issue on left join

Mon, 16 Apr 2012 10:00:05 GMT

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.

Re: The issue on left join

jagan — Mon, 16 Apr 2012 10:05:18 GMT

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.