Skip to main content
Announcements
Global Transformation Awards submissions are open! SUBMIT YOUR STORY
cancel
Showing results for 
Search instead for 
Did you mean: 
Sudarshan
Contributor II

Creating a dimension using Chart Function and Data Load Editor produces different results

Hi All, 

I am trying to fill nulls on a dimension field (Date Field) - called Financially Closed Date(FCD) and return new field as FIXED FCD. But it seems to produce different results when i do this in chart function and in data loader. Can someone help me point what I am missing here

 

Here's some example data: 

Sudarshan_1-1686400190150.png


As you can see there are some nulls in the column FCD. So to fix this, i use the following expression in the straight table: 

=if(IsNull(FINANCIALLY_CLOSED_DATE)=-1,date('12/31/2016'),date(FINANCIALLY_CLOSED_DATE))

And this results in a proper fixed column, like in the image below:

Sudarshan_2-1686400313188.png

But since I have to perform further transformations on the FIXED_FCD Field, I would like to add to the data loader. But When i execute the following statement in Data Loader: 

Sudarshan_3-1686400415779.png

It starts plotting a lot more rows than were present before with a whole lot of new dates:

Sudarshan_4-1686400591479.png

 

Thanks in advance guys! Any suggestions would be appreciated! 

 



 



 

Labels (4)
1 Solution

Accepted Solutions
vinieme12
Champion III

I believe your data model has a cross join between the two tables loaded and is showing data from both the tables.

 

Drop the table [Installation_Projects] after loading table [Preceding Load Test] using below statement

Add below line after resident load

 

Resident [Installation_Projects];

Drop Table [Installation_Projects];

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

4 Replies
vinieme12
Champion III

I believe your data model has a cross join between the two tables loaded and is showing data from both the tables.

 

Drop the table [Installation_Projects] after loading table [Preceding Load Test] using below statement

Add below line after resident load

 

Resident [Installation_Projects];

Drop Table [Installation_Projects];

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Sudarshan
Contributor II
Author

Hi Vinieme12 ,

Thanks for the solution. It works! But if i need to do more transformation and need some more fields from the table [Installation Projects], then should the logic be this way? :

1) First Load [Installation Projects], for making the Preceding Load Calculation and other calcs.! Then I drop  [Installation Projects] to use the new Preceeding Load. 

To use the  [Installation Projects] further: 

2) I load the table  [Installation Projects] again and left join 'Preceding Load' and Installation Projects again for adding the field FIXED_FCD again to the original table!?

Sudarshan
Contributor II
Author

Hey Vineeth,

Thanks for the supporting links, appreciate it! I'll try out the code today and update you here. 

Have a nice day! 

Sudarshan