IsNull() from Excel data issue

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

IsNull() from Excel data issue

Post by jackson.eyton »

Hi Everyone,
I'm running into an issue when pulling data from an excel file. Basically I am trying to pull in the data and use a Transform stage to split out rows if certain cells are empty. When I debug this I can see the incoming value from the excel file for these shows as <NULL>, I would assume I could then therefore use a constraint like IsNull(Link.Cell) and map in the columns. Unfortunately I can't get it to trigger. The incoming columns are set as Nullable, and the outgoing of the main link has them set to not nullable. The second link where I have the contraint and I'm trying to catch rows with null values, has the columns set as nullable.

I've tried other variations as well, (link.cell = 0), (link.cell = ''), etc. Does anyone have any ideas?
-Me
sriven786
Participant
Posts: 37
Joined: Wed Nov 08, 2017 1:36 pm

Re: IsNull() from Excel data issue

Post by sriven786 »

Check with the system whoever is creating the excel if they are correctly populating the NULL Values (or Add Constraint <> '<NULL>' and see if the data is getting Filtered out by the constraint. I tried sample ds job to create unstructured data with 1 Column as NULLABLE Yes and set to SETNULL() and those rows got successfully filtered out in next job if add constraint: ISNULL(Column)
Venkata Srini
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

Post by jackson.eyton »

I ended up just using rejects instead of constraints, It seems to be catching the entire rows where one of the cell values was null. I have this going to a CSV and that will be emailed to the client to review for remediation on there end if such a case arises. This should work just fine.
-Me
Post Reply