Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Specialist III
Specialist III

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;

View solution in original post

4 Replies
nagaiank
Specialist III
Specialist III

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
Author

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
Author

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

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