How to merge two table

[Anonymous]

Hello,

I wanna merge two table with tMap. The output and input table have the same structure. My second input table B (the lookup) includes some of these columns (in total 5). When there is a match (primary key is a ID-Number)  between the lookup and the second input table A, I want the values of the lookup table B for these five columns in my output. Otherwise the values of the second input table A. The remaining columns (which are not part of the lookup) always get data from table A.

I know I can use a expression. Something like "tableA.value1 =! null?tableA.value1 : tableB.value1". But that wouldn't be the best solution in my situation. Is there another way?

cheers,

Christian

[Anonymous]

Hi Christian,

By far the simplest solution is the one you've suggested yourself - to use a ternary expression in the relevant output columns of a tMap.

If you could explain why this isn't a good option for you, then I'm sure we could provide alternatives, but they're almost certainly going to be much more work.

Regards,

Chris

[Anonymous]

Hello,

I did it now with expressions. It works so far, but as I expected I have some trouble with some of my variables/columns. 

Example:

Input table A has the column permanentworker. This column includes permanentMale and permanent Femal workers. The new datasource, Lookup table B, have permanentMaleWorkers, permanentFemaleWorkers, permanentWorkers in seperate tables. So I have to calculate permanentWorkers with permanentMale and permanentFemale. This is the expression I'm using right now: 

((row2.PermanentMaleWorkers!= null?  row2.PermanentMaleWorkers : 0) + (row2.PermanentFemaleWorkers!= null?  row2.PermanentFemaleWorkers : 0)) 

Problem: The enduser can enter nothing (null value, this is the case when he doens't have to enter the question), 0  or a number. And only when we have the null value case, I get my data from table A instead of B. With my expression it's always null...

I've tried this one:

((row2.PermanentMaleWorkers!= null?  row2.PermanentMaleWorkers : 0) + (row2.PermanentFemaleWorkers!= null?  row2.PermanentFemaleWorkers : 0))

!= null ?

((row2.PermanentMaleWorkers!= null?  row2.PermanentMaleWorkers : 0) + (row2.PermanentFemaleWorkers!= null?  row2.PermanentFemaleWorkers : 0)) 

:

(row1.permanentWorkers != null? row1.permanentWorkers : null

But I know that it doesn't make sense, because the first part can't be null...

So for all that kind of questions, I have to take care that it can be a null value, 0 or a number for table A  and table B. But for the calculation I can't use the null values (null value + number doesn't work). 

When someone has a expression to sum two variables without the  ".. : 0" , that would be helpful.

Cheers,

Christian

[Anonymous]

Hi Christian,

Whilst it's possible to deal with nulls and default values using ternary expressions in the tMap; as you've found, it quickly gets very messy and difficult to manage.

I'd suggest adding a tReplace component before the tMap, and using this to replace nulls in the relevant fields with 0, then everything in your tMap should become a lot more straightforward.

Regards,

Chris

[Anonymous]

The problem is I need the null values in most of the fields: Otherwise you can't see if he answers the question with a 0 (so they don't have permanent workers) or nothing (null value: he doesn't has to answer the question). There's no way around. Replacing the null values don't work.

Cheers,

Christian

[Anonymous]

Hi Christian,

The only way to include a variable which could possibly be null in a mathematical expression like this, is to use a ternary expression (or suitable function) to convert it to 0 when it's null. This can either be done in the expression itself, which gets messy, or beforehand using a tReplace or something similar. If you also need the original value, without changing your job structure, then you'll need to use the ternary operator to achieve this.

How are you handling cases where the user hasn't provided a response?

The tReplace will make the expressions in your tMap simpler, but if you're going to need to know if they didn't provide a response at all later in your job, then it would make sense to have a Boolean field indicating this. This field doesn't have to be output, and can just be used for filtering or in your other logic.

Boolean fields can be added to your flow to indicate that the user provided no response to each question, using another tMap. You could then use the tReplace (or as you're only dealing with the null side of things, this could be done in the first tMap) so the expressions in your following tMap can be kept tidy.

For something as simple as this though, it really shouldn't be necessary to go to such lengths, and I suspect a slight redesign of the job might be a better option.

Can you provide screen grabs of your job and configuration of the various components? Hopefully we can then work something out which doesn't involve all of this messing about.

Regards,

Chris

[Anonymous]

First of all thanks for your help. I will try to shortly explain what my jobs are doing. I have several old reports which are created with SQL queries and published throw Jaspersoft Studio (Frontend Tool). We have now a new datasource. A wordpress template that includes a questionnaire plugin. The questionnaire is quite big (about 100 questions). In the end you get a cutout of the submitted questionnaires ( now called entries). I safe the output in the SQL table "newData". Each row is one entry and the columns are the questions which are necessary for  our reports. You can see the extraction job in the screenshot Extract_json. 

Then we have several subjobs. I have a job for each report. The old reports  which are generated with the SQL queries still have a lot of entries. The structure of these reports is similar to the new reports. Some questions/columns are the same, we only have a new datasource (wordpress), some new columns/questions, some columns are removed, respectively they are still there but from now on they don't get data anymore for the new entries. As you can see I have to take care about the old reports. There are still some more problems with these reports... the list is very long. But I have to work with them. Now I have to combine the old reports with the new datasource /columns.

This is what you can see in newdata_ReportC. Now I have some columns in the final report ( Output in tMap = final report) with some "more complex" expressions. In most of the fields I'm uisng the expression:

tableB.WorkersLivingOnArea_peak !=null? tableB.WorkersLivingOnArea_peak : (ReportC.number_of_workers_living_on_area !=null? ReportC.number_of_workers_living_on_area : null) 

Works fine as long as you don't have any special expressions in the Lookup input (newData/tableB). The permanentworker case is an example.

Where I can't work with null values for different reasons (i.e. calculation). And the I have a problem. Because without null values the expression above doesn't work. 

I tried to visuliaze the final report to make it clearer "report_sample" screenshot.

Some additional information:

- 99% of the fields in newData have null values (because I have to use them my solution)

- old reports ( 0 and null value exists for most of the columns)

I hope this helps. 

Cheers,

Christian

Extract_Json.jpg
newdata_ReportC_job.jpg
report_example.jpg