Outer join and case statement

[#1]

Hi,

I have an issue that am struggling to find a solution.

I have two huge qvd files(3 Gb each) from two tables orders, payments

sample date with just the coulms of intereset is below

orders:

pay_method, orderdate, visitnum

A, 1-Jan-13, 123

A, 1-Jan-13, 234

B, 1-Jan-13, 345

G, 1-Jan-13, 456

G, 1-Jan-13, 567

payments:

visitnum, paydate

123, 1-Jan-13

345, 2-Jan-13

456, 5-Jan-13

I want to join these two tables based on visitnum. In the output, I need paydate by default for any mapping records. If there is no mapping I need to get the orderdate

from orders

The output I need should look like below

pay_method, date, visitnum

A, 1-Jan-13, 123

A, 1-Jan-13, 234

B, 2-Jan-13, 345

G, 5-Jan-13, 456

G, 1-Jan-13, 567

I feel using a case statement in an outer join can give me the required output, but these are huge tables with millons of rows, so I do not want to join the tables in SQL.

Can anyone suggest a way to do this is qlikview while reloading??

Any help is greatly appreciated.

[nagaiank · Accepted solution]

The following script gives the result:

Left Join (orders) LOAD visitnum, paydate Resident payments;

DROP Table payments;

Result:

NoConcatenate LOAD pay_method, visitnum, If(IsNull(paydate),orderdate,paydate) as date Resident orders;

DROP Table orders;

[#3]

the two tables(qvd) are very huge around 3Gb each. Using a Resident might cause performance problems.

Can we get away with Resident?

[#4]

Hi krishnamoorthy,

Can you please let me know if using the resident tables causes performance impact as that data is originally loaded from huge qvd files?

If so, do we have to write the output to a seperate qvd and again load the qvd?

what is the beast approcah?

As I am new to qlikview any suggestions would help.

Thanks in advance

[udit_kumar_sana]

Hi,

Link the two tables with forming key i.e

orders:

visitnum &'-'& floor(orderdate) as Key,

visitnum,

pay_method,

orderdate as order_Date,

visitnum

payments:

visitnum &'-'& floor(paydate) as Key,

paydate as pay_date

and then in front end you can use If(IsNull(pay_date),order_Date,pay_date) to get one date

Regards,

Udit