4 Replies Latest reply: Apr 22, 2018 7:12 PM by Ron Campbell RSS

    Different calculated fields for a single record

    Al Sellaro

      Hi

       

      I am struggling with something that seems quite simple. I couldn't find any reference on how to solve it, though. I need to have one single record in my final_table that has four calculated fields.

       

      The final_table must have records that include the following information about the client:

      1. Client name
      2. Ticket number relative to Problem type 1
      3. Status relative to Problem type 1
      4. Ticket number relative to Problem type 2
      5. Status relative to Problem type 2

      I wrote the script below, but instead of having one record per client, the result is two records per client. The first record has information for fields 1, 2, and 3 above (and no info for fields 4 and 5) , whereas the second record has the information about fields 1, 4, and 5 (and no info for fields 2 and 3).

       

      Below is the script:

       

      temp_table:
      LOAD
          [ticket],
          [status],
          [name]
      
      FROM [lib://abc.qvd]
      (qvd);
      
      LEFT JOIN(temp_table)
      
      LOAD
          [ticket],
          [problem_id]
      
      
      FROM [lib://def.qvd]
      (qvd);
      
      
      final_table:
      Noconcatenate
      LOAD
          // Base fields
          [name] AS [client_name],
        
          // ----------------------------------------------------------------------------------------
          // Business logic
          // ----------------------------------------------------------------------------------------
          if([problem_id] = '1234', [ticket]) AS [client.prob1_ticket],   
          if([problem_id] = '1234', [status]) AS [client.prob1_ticket_status],
          
          if([problem_id] = '5678', [ticket]) AS [client.prob2_ticket],
          if([problem_id] = '5678', [status]) AS [client.prob2_ticket_status]
          
      RESIDENT temp_table;
      
      Drop Table temp_table;
      
      
      

       

      Consider as an example the following information for the temp_table:

      ticketstatusnameproblem_id
      T1234In progressClient A1234
      T5678ClosedClient A5678
      T4321OpenClient B1234
      T8765In ProgressClient B5678

       

      This is the expected result for the final_table:

      client_nameclient.prob1_ticketclient.prob1_ticket_statusclient.prob2_ticketclient.prob2_ticket_status
      Client AT1234In ProgressT5678Closed
      Client BT4321OpenT8765In Progress

       

      The result the script about is yielding is the following:

      client_nameclient.prob1_ticketclient.prob1_ticket_statusclient.prob2_ticketclient.prob2_ticket_status
      Client AT1234In Progress--
      Client A--T5678Closed
      Client BT4321Open--
      Client B--T8765In Progress

       

      Thank you.

        • Re: Different calculated fields for a single record
          Ron Campbell

          Hi Al

           

          I'm thinking that in your final _table load statement that you might need to split this into two with a join as in your sample data both clients have an example of each problem and for each of the if() statements in the load you have an implicit else that returns a null.

          So to split:

          1. final_table: 
          2. Noconcatenate 
          3. LOAD 
          4.     // Base fields 
          5.     [name] AS [client_name], 
          6.    
          7.     // ---------------------------------------------------------------------------------------- 
          8.     // Business logic 
          9.     // ---------------------------------------------------------------------------------------- 
          10. [ticket] AS [client.prob1_ticket],    
          11. [status] AS [client.prob1_ticket_status], 
          12. RESIDENT temp_table
          13. Where [problem_id] = '1234'
          14. Join(final_table)
          15.     [name] AS [client_name],       
          16. [ticket] AS [client.prob2_ticket], 
          17. [status] AS [client.prob2_ticket_status] 
          18.      
          19. RESIDENT temp_table
          20. WHERE [problem_id] = '5678'

           

           

          So this also takes the if()'s out and replaces them with a where clause in the resident load.

          I'm not a fan of data tables being laid out like pivot tables but I presume you have a good reason for it.

          Let me know if this makes sense/helps

           

          Ron