Transform Joined Data

[Former Member]

I am using BLS to combine 2 queries from different data sources. Query 1 is my primary data source and Query 2 is secondary. I am using the Joiner action to join the data between these 2 queries, but recevie "NA" for all columns retrived from Query 2 that do not match a row in Query 1.

How can I "transform" a column in Query 2 to "0" if there is not a matching record? I am also performing cacluations on this combined dataset and need to have "0" value for non-matching rows so that the calculation can function.

I appreciate any assistance you can provide!

Larry

[Former Member · Accepted solution]

Larry,

To better assist you, can you please provide the joiner output with a few rows of data?

Regards,

Michael Teti

Senior Solutions Architect

Infodat International, Inc.

mobile 713.598.8352

email michael.teti@infodatinc.com

web www.infodatinc.com

[Former Member]

Hi, Larry.

Use an "outer join" (see the configuration dialog for more info) and you should be OK.

- Rick

[Former Member]

Rick,

I had a doubt here.

Going by SQL rules an outer / full outer join will always return "null" (which explains the NA) for the rows with unmatching value.

So my guess is NA can only be avoided if you have an Inner join.

I am not sure if we can avoid the NA in Larry's case (which seems to be an Outer OR a Full Outer join).

Udayan

[Former Member]

Rick,

I have the Joiner action set up as an "Outer Join" but do not see any additional information on the configuration tab other than choosing the 2 data sources and linking the parent and child columns. Am I missing something? We are on version 11.5.1 b63.

I appreciate your quick response, but did not see any indication that there are additional parameters for non matched data.

Thanks,

Larry

[Former Member]

Udayan,

Unfortunately an inner join only returns rows where a match is in both sources. I need everything in Query 1 and only those rows in Query 2 that match. If no match, I would like 0 for numeric columns so that I can perform calculations.

Any suggestions?

Thanks,

Larry

[Former Member]

My guess is that you would be performing the calculations only after the joiner action, right?

So when you are running through each row of the Joiner XML output then you can use a condition action (if NA then 0) to accomplish your task.

This is what I can think of .

People, any simpler ideas to accomplish this?

Udayan

[Former Member]

Udayan,

By BLS Logic is currently as follows:

1. SQL Query actions (2 queries)

2. Joiner - references queries

3. Calculation - references joiner

4. Repeater - references calculation

5. Tracer (will convert to iDoc when working). - references repeater

I think what you are indicating is:

1. SQL Query actions (2 queries)

2. Joiner - references queries

3. Condition - references joiner

4. Calculation - references condition

5. Repeater - references calculation

6. Tracer (will convert to iDoc when working). - references repeater

Thanks for your help!

Larry

[Former Member]

Well, I was thinking the repeater would be after the Joiner (but definitely you understand your requirement better!).

But if that solved your problem, good enough!

Udayan

[Former Member]

Udayan,

Unfortunately that did not solve the problem. My current sequence is as follows:

1. 2 Queries run simultaneous

2. Joiner to join query columns – references sequence 1

3. Repeater to process the joined queries – references sequence 2

4. Conditional action to check if the OQQuantity is NA then change to 0 on True side of condition, otherwise do nothing - references sequence 3

5. Calculate net production and quality production - references sequence 3

6. Trace to see if values are correct - references sequence 5

This process does not provide any data in the trace. I am still having problems getting this process to execute properly.

I appreciate any additional assistance you can provide.

Larry

[Former Member]

Larry:

If you can post snippets of actual data in XML form (both queries) and a definition of your calculations and what you're trying to achieve, I'm sure I can come up with a solution for you.

- Rick

[Former Member]

Hi larry,

I guess the problem is,

4. Conditional action to check if the OQQuantity is NA then change to 0 on True side of condition, otherwise do nothing - references sequence 3

5. Calculate net production and quality production - references sequence 3

Calculate net production and quality production is added where?

Is it on the true side of your action block or false side? <b>Better add this action block in the default.</b>

After condition action block, true side will change NA to 0 and False side a blank sequence.

Add one more sequence to the same action block. This is default sequence.

<b>One more suggestion:</b> Instead of using calculation use calculated columns actions. So that you can avoid the repeater action.

Hope this helps you,

Regards,

Kishore

[Former Member]

Kishore,

I currently have the following structure:

1. 2 Queries run simultaneous

2. Joiner to join query columns – references sequence 1

3. Repeater to process the joined queries – references sequence 2

4. Conditional action to check if the OQQuantity is NA

5. True: Change to OQQuantity from Repeater to 0

6. False: Do nothing

Default Path.

7. Trace to see if values are correct - references Repeater data but I still see NA for the OQQuantity column.

How and where should I be reassigning the OQQuantity column so that I get either the value of the column or 0 if the vaule is NA. Once I see this change in the Trace, then my objective is to perform the appropriate calculations for Net Production and Quality Percentages.

I appreciate any assistance you can provide.

Thanks,

Larry

[Former Member]

Larry

You would still see NA because I guess after the condition is true you are definitely not editing the XML (the output from the Joiner). [What do you exactly do when you say"Change to OQQuantity from Repeater to 0"]

It would help if you let us know the kind of calculation you want to do after the join.

If it is just some quantity that you want to accumulate then you might do that after the conditional block itself.

Take a local variable with initial value as zero.

If condition is true do nothing else add OOQty to the local variable.

After repeater is through the local variable is your accumulated Qty.

Makes sense?

Udayan

[Former Member]

Hi,

After Checking the value is NA or 0, , in the true path assign the OQuantity as 0. You assign the 0 value which is coming from repeater.

True path OQuantity column come from repeater. after changing this use <b>the same repeater column to calculate</b>.

I hope this helps you.....

Regards,

Kishore kumar