Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Different calculated fields for a single record

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.

1 Solution

Accepted Solutions
campbellr
Creator
Creator

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

View solution in original post

4 Replies
campbellr
Creator
Creator

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

Anonymous
Not applicable
Author

Hi, Ron

Thanks for the suggestion. I believe that it does not solve the problem, though. Conceptually, it makes sens, but when I try to do the join against the same table, the script simply halts there. There is no error, but the script execution does not continue.

I am testing a solution based on mapping. I will post here the results so that other people can benefit from this research.

Once again, thank you very much for your suggestion.

Anonymous
Not applicable
Author

The final solution was not based on mapping either. What I did to solve the problem was to create different temp tables, one for each step of the JOIN, and then join each individual temp table to the final one. This is basically your solution, Ron, but NOT joining the final table with itself. The reason for that is to avoid cartesian product of the final table.

campbellr
Creator
Creator

Hey Al

Good next step, QS seems a bit particular about what you can include in a join, mostly just straight loads or resident loads. Glad I managed to point you in the right direction anyway.