Control Null values in a table with Previous values

[Former Member]

Hi Team ,

We are into a tricky situation and hoping some inputs.

We have two tables say A and B

They have a Common filed A.controlId and B.control ID on which that are Joined through a Outer Join

A is snapshot table and have only a Particular time range data current .

Fields in Table A will have Only one record say for an account number

Control ID

Created Date

Status

Account Number

Fields In Table B SCD will contain multiple records for an account with there status and previous status

Control ID

Created Date

Previous Status

Account Number

In table B the data is not pushed out on regular basis and we can have Null value for Previous status.

Issue is because Of data not available at times in table B , an account number can show null values for Previous status, we are looking for a Solution through which it should automatically take previous status values if there is null value's

Can this be done at Universe level ?

[Former Member]

Hi,

You can create a Derived table from Table B where you will just add the condition in Where Clause of that SQL as PreviousStatus In Not Null and CreateDate=Max(CreateDate) for that Control Id.

So this derived table will always pull the latest Non Null Previous Status.

Hope this helps.

Note: This is just a pseudo code you may have to convert that into valid SQL statement.

Regards

Niraj

[Former Member]

yes Niraj's solution will work.

Pls refer to below sample data that I generated :-

Table Name: test1

The query for derived table would be :-

select 1.control_id,t1.account_num, t1.created_date, t1.prev_status,t5.prev_status 

from test1 t1, ( select t4.control_id,t4.account_num,t4.prev_status

                        from (select t2.control_id, t2.account_num,max(t2.created_date) created_dt from test1                                    t2 where t2.prev_status is not null group by  t2.control_id, t2.account_num ) t3,

                                test1 t4

                        where t3.control_id = t4.control_id

                        and t3.account_num = t4.account_num

                        and t3.created_dt = t4.created_date) t5

where

t1.control_id = t5.control_id

and t1.account_num = t5.account_num;

Here's the output of the Derived table query :-