1 Reply Latest reply: Jul 29, 2011 3:28 PM by Patricio Solari RSS

    Data Management Puzzle

    Jamie Weber

      I'm wondering how the Qlikview community would approach the following puzzle. I have a working solution, but it is not elegant by any means.

       

      I have two tables outlined below, table1 and table2. I want to create a single table that looks like table3.

       

      The field id is a made up identifier for this example. num only appears on table1. bnum only appears for colors equal to blue. The only color in table2 is blue. letter sometimes exist in table1, but then only for color equal red. letter exists for each record in table2.

       

      Effectively, table3 should contain all records from table1, add records from table2, and fill in letter where it exists.

       

      table1

      id

      num

      bnum

      color

      letter

      1

      A

       

      red

       

      2

      B

       

      red

      x

      3

      C

      a

      blue

       

      4

      C

       

      red

       

      5

      D

      b

      blue

       

       

      table2

      id

      num

      bnum

      color

      letter

      3

       

      a

      blue

      y

      5

       

      b

      blue

      y

      5

       

      b

      blue

      z

      6

       

      c

      blue

      x

       

      table3

      id

      num

      bnum

      color

      letter

      1

      A

       

      red

       

      2

      B

       

      red

      x

      3

      C

      a

      blue

      y

      4

      C

       

      red

       

      5

      D

      b

      blue

      y

      5

      D

      b

      blue

      z

      6

       

      c

      blue

      x

       

        • Data Management Puzzle
          Patricio Solari

          Here you have a way that should work.:

           

          TempTable:
          LOAD
               id,
               num,
               bnum,
               color,
               letter
          FROM
          [C:\Documents and Settings\psolari\My Documents\Tables.xls]
          (biff, embedded labels, table is Table1$);


          Table2:
          OUTER JOIN LOAD
               id ,
               num as num2,
               bnum as bnum2,
               color as color2,
               letter as letter2
          FROM
          [C:\Documents and Settings\psolari\My Documents\Tables.xls]
          (biff, embedded labels, table is Table2$);


          Table3:
          LOAD
          id,
          If (len(trim(num)) <> 0, num, num2) as num,
          If (len(trim(bnum)) <> 0, bnum, bnum2) as bnum,
          If (len(trim(color)) <> 0, color, color2) as color,
          If (len(trim(letter)) <> 0, letter, letter2) as letter
          Resident TempTable
          ;

          Drop Table TempTable;