Different calculated fields for a single record

[Anonymous]

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:

ticket	status	name	problem_id
T1234	In progress	Client A	1234
T5678	Closed	Client A	5678
T4321	Open	Client B	1234
T8765	In Progress	Client B	5678

This is the expected result for the final_table:

client_name	client.prob1_ticket	client.prob1_ticket_status	client.prob2_ticket	client.prob2_ticket_status
Client A	T1234	In Progress	T5678	Closed
Client B	T4321	Open	T8765	In Progress

The result the script about is yielding is the following:

client_name	client.prob1_ticket	client.prob1_ticket_status	client.prob2_ticket	client.prob2_ticket_status
Client A	T1234	In Progress	-	-
Client A	-	-	T5678	Closed
Client B	T4321	Open	-	-
Client B	-	-	T8765	In Progress

Thank you.

[campbellr · Accepted solution]

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'; 
15. Join(final_table)
16.     [name] AS [client_name],       
17. [ticket] AS [client.prob2_ticket], 
18. [status] AS [client.prob2_ticket_status] 
19.      
20. RESIDENT temp_table
21. 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]

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]

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]

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.