Six Stars

can we have only auto-fixed rows in the output table

I am running a Job on which I have put some auto-fix condition o a column. I am getting the correct result, but in the output table can we have only those records on which the 'fix' was applied? Currently it is showing all the records. Because we may have thousands of records, so showing only those records where fix was applied would be very useful.

autofix.jpg

  • Data Integration
12 REPLIES
Eleven Stars TRF
Eleven Stars

Re: can we have only auto-fixed rows in the output table

Hi,

The simple way is to duplicate the flow (tReplicate) before the tMap to identify the rows corresponding with the replacement rule (using a tFilter for example).

Then on the next subjob, you can join the content of "tem_tabledata" with these candidates using a join in a tMap.

You will get what you expect.

 

Hop this helps.

 


TRF
Six Stars

Re: can we have only auto-fixed rows in the output table

Thanks for the reply but not very clear. Can you help me with a sample job?

Five Stars

Re: can we have only auto-fixed rows in the output table

Hi ,

You have to check input data name having null value are not ?

Count the not null values records in Input .

 

If the Rejected records in output you have to check the rejected flow in database output component.

 

Thanks,

Madhu. 

Six Stars

Re: can we have only auto-fixed rows in the output table

No, that's not what I am seeking. My concern is that only those records on which auto-fix has been applied should move to my output table. Null checks I can apply.

Ten Stars

Re: can we have only auto-fixed rows in the output table

Add a second tMap that uses an output filter comparing the original name to the (potentially) modified name.

Eleven Stars TRF
Eleven Stars

Re: can we have only auto-fixed rows in the output table

If you want get only the fixed row to the target database, add a tFilter before the tMap with the appropriate condition to select only the rows that will be fixed.

Base on your 1st example, rows which contain a digit are selected, others are not.


TRF
Five Stars

Re: can we have only auto-fixed rows in the output table

Hi ,

 

Use this function row1.Name != null && row1.Name.matches(".*[0-9].*")  in tmap filter expression.

Please check below screen shots for reference.

 

Six Stars

Re: can we have only auto-fixed rows in the output table

Ok Thanks for the reply.

Another scenario is can I have both the values i.e. the old value and the fixed value as two rows in the table without defining any new column for comparison purposes. Like currently I define a new column in the table to show the'fixed value'. This is to help my client to see both the records one after other and take a decision to approve the change or not. Please see the screenshots below.

fix1.jpgfix2.jpg

 

Six Stars

Re: can we have only auto-fixed rows in the output table

Any replies on this please?

Five Stars

Re: can we have only auto-fixed rows in the output table

Can you explain clearly with example what is the source and Target data ?  

Six Stars

Re: can we have only auto-fixed rows in the output table

Like as you see in my screenshot above, I had to define a new column to let the users see the old column value and new transformed value after the 'rule' applied. Instead is it possible to show in the output like one row as 'old record' and next row as 'new-modified record' fro comparison purposes. The schema is same for both 'source and 'target table' as it would be a temporary staging table.

Six Stars

Re: can we have only auto-fixed rows in the output table


ppp_rrr wrote:

[...] Instead is it possible to show in the output like one row as 'old record' and next row as 'new-modified record' fro comparison purposes. [...] 


Hi ppp_rrr,

my suggestion for this set-up is to use two outputs as shown in the image.


two outputs fixed and original valuetwo outputs fixed and original value

Direct the Outputs for example to HashOutputs or (temporary) files. Use HashInput or file input components to read the data and a tUnite component to combine these two inputs into one. (If you use two linked HashOutput components you can use just one HashInput for reading.) Then let the data flow to a tSortRow component, sorting first on "sequence_number", then on "flag_fixed" (asc or desc, depending on whether you want the fixed row first or the original row).

Please note that the sequence is generated in the "Var" panel, so that for one input row both outputs get the same sequence_number.

 

Best regards,

Thomas