4 Replies Latest reply: Mar 14, 2013 12:53 AM by Udit Kumar Sanadhya RSS

    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.