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

    Outer join and case statement



      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



      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



      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.