Null detection after joiner

[kumar_s]

Hi,

I have set defaults to all columns in previous jobs.
0," ",0001-01-01 for integer/decimal,varchar/char,date respectively.
In the current job i need to do a full outer join and check for New, changed, Delete, and Duplicate.
If i need to get the New Insert, i check for the previous defaults. But it is not working, so i checked for null still it is not woirking. so used the following code for that condition.

Code: Select all

Len(Trim(fieldname)) = 0

That worked.
But when i need to check null condition for date, i got pissed off......
i could not use "", or IS NULL, or previous defaults.
I tried using Default, Null Feild Value, and with Nullable = yes.
If i need to equate both record feildname.1 = feildname.2 and if it is null the conditon works unexpectedly.
May i know how to check null for dates and other data type.

regards
kumar

[ray.wurlod]

Please provide more information about "unexpectedly". What did you expect, and why? What does the help/manual say about the null handling functions? Are you expecting something else? What actually happens? how do you prove what actually happens?

And remember that "" is not the same as NULL. One knows what "" is (it's a string containing exactly 0 characters). One does not, can not, know what NULL is, simply because it's unknown.

[kumar_s]

Hi,
rather than giving u a blunt situation and expecting a solution, Let me expect what i need.
please let me know how to set a constraints in the transformer which is followed by a joiner (full outer join) to detect New,Copy,Edit,Delete condition.

Code: Select all

before file1------->
	                   joiner----->Transformer-------->output file
after file2------->

I should retains two columns from before file in case of copy or edit is concern.
like, create date which is populated in transformer (after file wont have create date, so during 'New' condition that column is populated with current date).
so when i get a copy records, i should send the before file record to output file.
similarly if i get edit records the creaet date should alone should get into output file.
In previous jobs i have defaulted the Null with some values like 0 for integer, " " for char/varchar and 0001-01-01 for date.
so please let me know what constraints should be there in transformer to get the right results.
PS: i have tried with change capture/chage apply, compare, difference and was not helpful in getting the before file records

regards
kumar

[ray.wurlod]

Three of the conditions are straightforward.

• IsNull(A) And IsNotNull(B)
  
  IsNotNull(A) And IsNull(B)
  
  IsNotNull(A) And IsNotNull(B) And ...

where ... is any other comparison needed to detect a change.
Outer joins return NULL for columns from rows (links) where there was no match.

[dsxdev]

Hi
For all nullable columns coming from source you can check for nulls using
IsNull() function. But or not null columns in numeric fields you get defaults values as 0 and for varchar fields it is ''.
So for not null varchar fields you get '' for non matching records.

[kumar_s]

Hi,
Even i was expecting it to be more straight forward and simple, But it isnt

Ray,
Before trying u r idea, i tired with the following
IsNull(A.Key) this is to give B records(Delete)
IsNull(B.Key) to give A records(New)
if A.Key = B.Key and A.Values<>B.Values(edit)
it doesnt work.
Even after i trying ur idea, no rec in output..........

Hi Dxdev,
i now got in to very basic granularity, I made the two file to joined as all Nullable column. and Output col as Nullable as well.
If i get 11 recods as output from full outer join, but i could see only 6

Code: Select all

records in output file. With some waring in director as 
LOD_DEP_AR,0: Field "CRT_TS1" is null but no null export handling is defined

should i have to make key col as not nullable....
(I tried even that i could not get all the 11 records in output.)

regards
kumar

[mpouet]

Hi,

I used change capture. I don't use PX for 4 month, but I remembered it works well. If the change capture allow to have only the new lines and the modified ones and you need to keep the before lines also, may be you should add a funnel. Analysing modified, new, old [...] lines by a join stage is long to do if you have a big number of columns, and a mistake is easy to do... Remember the KIS (Keep It Simple) !

Matthieu.

[kumar_s]

HI,
it is a file with bigger number of column, and change capture/change apply is not helping in my case.
if there is option with getting selected afer column, it could have solved my prob much earliar....

regards
kumar

[luie]

You should be able to get what you want with change capture. We have jobs with quite the same logic and it works fine. This is how we implement the job.

Code: Select all

after  ---> cdc  --> transformer
              ↑             ↓   ----> funnel    
before ---> copy -------> join

Split 'before' data so you can keep the old columns. In your transformer, new records (change_code=1) would go directly to the funnel while updates (change_code=3) go to the join stage - this way, you can keep old columns from your 'before' data.
I'm assuming here that you're dropping 'For COPY' and 'For DELETE' records. If you need those, then set the transforner constraints accordingly.

[jayantthakore]

Hii Kumar,
Wat I concluded from your situation is that..........
You need to put a constraint and find which record is a copy, or which need to be edited, or dropped.

You can check key column it and assign a stage variable as 1 if the record is new.

If key matches club all non key columns and then check it if it matches assign the same variable as 2 to reject the record.

And if non key doesn't match assign the variable as 3 to edit.

plz tell if it is correct

[dsxdev]

Hi,

I hope here the problem is with the null handling on file stage you need to
define null field value for the nullable fields.

In Full outer join can tell me how many records are there from left link and how many records from right link.

Take example

From left link and right link you have 5 records each and only 4 records from each link match then your output would be

4 matching + 1 non matching record from left link + 1 non matching record from right link.

which total of 6 records from 10 records input (right + left links)



If I understand the requirement
You have four types of records coming from two files
1. New records
2. Edit records
3. Delete records and
4. Copy records


You need to check which records is of which type.

Using a Change Capture and transformer you can identify these records along with all the columns from both the files.

In Change Capture Stage you have to set

Drop Output For Copy
Drop Output For Delete
Drop Output For Edit
Drop Output For Insert as False.

Then you get all the records in the out put with ChangeCode

For Copy records which are Duplicate based on keys and value columns.
Out of the change capture you get all columns from primary input.

Now using constraints on Change Code filter records which do not find a match in Change Capture that is delete records. Join these records with the secondary input (after link) Funnel the data from both transformer and Join stage. This would be your expected out put.

The design suggested by luie would be do.