Filling the nulls with dates

[Sameer9585]

Hi,

I have case where there are three columns with Product,ETL,Date and the condition is if the Product has an entry of the delete it has date respective to that so that date is to be filled at the Insert and Update entries.Please find the attachment for better understanding.

Thanks, 

[Mauritz_SA]

Hi Sameer

You can use this as a Dimension in the front end: =Aggr(ONLY({<ETL = {"Delete"}>}Date),Product)

The resulting table will look like this:

If you want to do this in the script then you can load the table, create a mapping table from the resident table but only loading the Product and Date where ETL = "Delete". Then you can use ApplyMap to get the value and a Null() value if nothing is found.

Hope this helps.

Regards,

Mauritz

[Sameer9585]

Can you provide me with script code it would more helpful.

Many Thanks,

[Mauritz_SA · Accepted solution]

Hi Sameer

Sure:

[Original Data]:
LOAD * INLINE [
Product,ETL,Date
A,Insert,
A,Update,
A,Delete,1-Jan
B,Update,
B,Delete,15-Jan
C,Insert,
C,Update,
C,Delete,10-Jan
D,Insert,
D,Update,
];

[Mapping Table]:
MAPPING
LOAD
Product,
Date
RESIDENT
[Original Data]
WHERE
ETL = 'Delete';

RENAME TABLE [Original Data];

[Final Data]:
LOAD
*
,APPLYMAP('Mapping Table',Product,Null()) AS DeletedDate
RESIDENT [Original Data];

DROP TABLE [Original Data];

Regards,

Mauritz