topic Re: Outer join and case statement in QlikView

Outer join and case statement

Wed, 13 Mar 2013 00:55:51 GMT

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.

Re: Outer join and case statement

nagaiank — Wed, 13 Mar 2013 01:08:46 GMT

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;

Re: Outer join and case statement

Wed, 13 Mar 2013 01:12:18 GMT

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

Can we get away with Resident?

Re: Outer join and case statement

Wed, 13 Mar 2013 18:11:21 GMT

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

Re: Outer join and case statement

udit_kumar_sana — Thu, 14 Mar 2013 04:53:38 GMT

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