Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
JacobTews
Contributor III
Contributor III

Mapping expression results not pushing through to data warehouse

I have updated the mapping for a particular field but can't figure out how to convince Compose to make the changes on previously written records. New records to the table are correct.

Testing the expression in the GUI gives the expected result:

JacobTews_0-1710522998871.png

 

However, after loading, only newly-written values are correct in the table in the data warehouse:

JacobTews_1-1710523046137.png

 

I have tried running an incremental load as well as a full load on the table, have cleared the metadata cache and the landing cache. It seems like I'm missing something obvious...any ideas?

Qlik Compose for Data Warehouses Qlik Compose 

Labels (3)
9 Replies
barbm
Contributor II
Contributor II

Jacob - It is the full load that will make the changes to the previously written records. Just make sure that mapping is included in the full load ETL set and that you've generated etl for that full load after making any changes.
JacobTews
Contributor III
Contributor III
Author

That's definitely what I expected, too.

I checked the task statements, and the function is called correctly when populating the staging table:

JacobTews_0-1710526153971.png

 

Not sure where to turn next...

JacobTews
Contributor III
Contributor III
Author

For the good of those following along at home, I also just tried the fake "Drop and Recreate" which is sometimes needed to fully clear the cache: in the project settings, select "Generate DDL scripts but do not run them," drop and recreate the data warehouse (which executes nothing but tells Compose that it did) to clear cache, then deselect the "Generate DDL..." option.

Alas, this was unsuccessful in solving the problem.

JacobTews
Contributor III
Contributor III
Author

An update today:

I went through the full load ETL tasks one SQL statement at a time, and the behavior still doesn't make sense to me. I would expect that running a full load would result in 'RED WILLOW' being updated with 'Red Willow', but this is not what is occurring. The vast majority (93%) of the records in the table remain as originally written.

@Dana_Baldwin any ideas what I'm doing wrong here? I would like to understand how to do this correctly, because there are already additional updates that I need to make to other tables and columns, and almost certainly more in the future. Thanks!

 

JacobTews
Contributor III
Contributor III
Author

@shashi_holla any thoughts here?

JacobTews
Contributor III
Contributor III
Author

Well, everyone, with the help of @Suresh_Kumar I now know what the problem is:

The function I am using transforms a string into Proper Case, but SQL Server is case-insensitive, so Compose does not detect the casing as a change and thus does not update any values.

Haven't decided how to solve it yet, but that's the issue, for my legions of fans following this thread! 😆

sureshkumar
Support
Support

Hello @JacobTews 
Thank you, one of the customers had similar issue and which was addressed in a below community post

https://community.qlik.com/t5/Qlik-Compose-for-Data-Warehouses/Case-Insensitivity-in-SQL-Server/m-p/...

 

Regards,

Suresh

JacobTews
Contributor III
Contributor III
Author

That was helpful; thanks for the tip, @sureshkumar.

In my case, the source for the fields in question is always uppercase, so I manually updated the existing records (in SSMS) and now new records flow through using the ToProperCase UDF.

sureshkumar
Support
Support

Hello @JacobTews 

If my previous post helps, then kindly Accept it as Solution.

 

Regards,

Suresh