Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Outer join and case statement

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.

1 Solution

Accepted Solutions
nagaiank
Valued Contributor III

Re: Outer join and case statement

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;

4 Replies
nagaiank
Valued Contributor III

Re: Outer join and case statement

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;

Not applicable

Re: Outer join and case statement

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

Can we get away with Resident?

Not applicable

Re: Outer join and case statement

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
Contributor II

Re: Outer join and case statement

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

Community Browser