2 Replies Latest reply: Nov 15, 2012 10:30 AM by Francesco Puppini RSS

    IF NOT EXISTS - Do the fields need to have the same name?

    Francesco Puppini

      Hi Everybody,

       

      maybe someone help with the "IF NOT EXISTS" syntax. Going through the previous posts, I have noticed that this EXISTS command makes the assumption that the field must have the same name (or, did I get It wrong?). But this implies that the model will make an association, or even a concatenation. But instead, I want to keep the fields independent.

       

      I have created a nice example about a dance competition. This example reproduces EXACTLY the business problem that I have to solve at work. Here it is.

       

      THE PROBLEM:

       

      There is a dance competition. Only the first 3 solo dancers (male or female) and the first 3 couples will win the admission to the final.

       

      The solo competition is the most important. If a dancer is participating and winning in both, he will be automatically admitted to the solo final, and his couple will be eliminated.

       

      The jury is issuing a ranking of the first 6 solo dancers and the first 6 couples. The task is to present the list of the 3 solo winners and the 3 couple winners, so that 9 distinct people can be sent to the final.

       

      Here is the code:

       

      SoloRanking:
      LOAD * INLINE [
      SoloRankNo, DancerNo
      1, 15
      2, 27
      3, 33
      4, 40
      5, 41
      6, 42
      ];


      CoupleRanking:
      LOAD * INLINE [
      CoupleRankNo, CoupleNo, MaleDancerNo, FeMaleDancerNo,
      1, C03, 15, 50
      2, C12, 51, 52
      3, C15, 53, 54
      4, C18, 55, 27
      5, C31, 56, 57
      6, C36, 58, 59
      ]; 

       

       

       

      The desired output for the CoupleRanking is C12, C15, C31 (the couples C03 and C18 are eliminated, because one of the 2 dancers won the other competition)

       

      My problem is how to discard the couples C03 and C18?!

       

      Can anyone help?

       

      Thanks,

       

      Francesco

       

       

      XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

       

       


      NOTE:


      Without using the EXISTS command I have solved the problem as follows. But I am sure that there is a much more elegant solution..


      MY TEMPORARY SOLUTION:

       

      AllDancers:
      LOAD
      MaleDancerNo AS Dancer
      RESIDENT
      CoupleRanking;

       

      Concatenate

       

      AllDancers_concat:
      LOAD
      FeMaleDancerNo AS Dancer
      RESIDENT
      CoupleRanking;

       

      LEFT JOIN (AllDancers)

       

      DancersToExclude:
      LOAD
      DancerNo AS Dancer,
      'Exclude' AS FlagToExclude
      RESIDENT
      SoloRanking;

       

       

      ValidCoupleRanking:
      LOAD
      CoupleRankNo AS [Valid Couple Rank No],
      CoupleNo AS [Valid Couple No],
      MaleDancerNo AS [Valid Male Dancer],
      FeMaleDancerNo AS [Valid Female Dancer]
      RESIDENT
      CoupleRanking;

       

      INNER JOIN (ValidCoupleRanking)

       

      ValidMaleDancers:
      LOAD
      Dancer  AS [Valid Male Dancer],
      'Valid Male'  AS MaleDancerValidFlag
      RESIDENT
      AllDancers
      WHERE LEN(FlagToExclude)=0
      ;

       

      INNER JOIN (ValidCoupleRanking)

       

      ValidFemaleDancers:
      LOAD
      Dancer  AS [Valid Female Dancer],
      'Valid Female'  AS FemaleDancerValidFlag
      RESIDENT
      AllDancers
      WHERE LEN(FlagToExclude)=0
      ;

       

      DROP TABLE AllDancers;