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

[Sudarshan]

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: 

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:

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: 

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

Thanks in advance guys! Any suggestions would be appreciated! 

[vinieme12 · Accepted solution]

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];

[Sudarshan]

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!?

[vinieme12]

Not really, you can add as many derived fields or stack multiple preceding loads

refer below

https://community.qlik.com/t5/Design/Preceding-Load/ba-p/1469534https://community.qlik.com/t5/Design/Preceding-Load/ba-p/1469534

https://www.quickintelligence.co.uk/preceding-load-qlikview/

https://analyticshub.io/2020/08/16/demystifying-preceding-load/

https://www.quickintelligence.co.uk/preceding-load-qlikview/

[Sudarshan]

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