Prepare

Hi,I have added a new field “PCD_Prev” to my table and populated this field using the LAG function (Custom transformation) Like so:LAG([PCD], 1, '0') OVER (Partition BY [AdministrationID], [ApplicationID] ORDER BY [AcceptanceID] )Now I try to use this field in a case statement of another field, again using Custom transformation, like so:COALESCE(SUM(CASE         WHEN [PCD_Prev] in ('0', 'ka_aa')             THEN 1                 WHEN ([PCD] = 'ka_hb' AND [PCD_Prev] = [PCD] )             THEN 1        ELSE 0          END        ) OVER(partition BY [AdministrationID], [ApplicationID]          ORDER BY [AcceptanceID]        ROWS between unbounded preceding and current row          ), 0)I get an error that I can't use a window frame in another window frame although I don't do it in the same transformation.. Is this standard behavior? Do you have a solution for this?In SSMS

Normally I’m using the default type:Can anyone explain the different behaviors?

We are facing a performance issue while fetching the data from D365 F&O using CData provider. While fetching a data entity with around 700K records, It was taking around 1:20 mins, now when we execute it, its running for 10+ hours with no response. While executing we noticed a SQL session with bulk insert command from TimeXtender with suspended state. and on the D365 database there are multiple session of batch process to fetch the data based on partition number. Since we are witnessed multiple sessions keeps executing, we would like to understand how the batch size is defined. And how we can customize the batch. Or any other way to set the connection properties which pull’s the data faster.Query for Ref :FROM Table_XXXXXXX T2 WHERE ((((((T2.PARTITION=5637144576) AND ((T2.PARTITION#2=5637144576) OR (T2.PARTITION#2 IS NULL))) AND (T2.PARTITION#3=5637144576)) AND (T2.PARTITION#4=5637144576)) AND (T2.PARTITION#5=5637144576)) AND (T2.PARTITION

I’m importing a table which is supposed to have an integer primary key, but the data is messy and we find things like “6TEST3” or “CREDIT3” in this field. I want to read the table and use this field as an integer, however can’t convert it because of those rows. Is there a way to remove a row if I can’t convert a field value to integer?

In v20.10.43, I’ve come across a bunch of auto-generated views with a _BCV suffix.Are they used for merged (incremental?) loading in the next layer, or what’s the purpose of these views?Why do they need to be redeployed whenever I do a managed deployment?And why does the SQL Clean Up Wizard delete them when they’re being used?(I haven’t tested if this could happen in a v6284.1 MDW)

Hi,To my knowledge it is not possible to setup alerts for errors or warnings on validation rules.However, I read the following on the TimeXtender Sales Hub:"Set up automated alerts to notify you when data quality rules are violated, data quality issues are detected, or executions fail to complete.” (https://coda.io/d/TimeXtender-Sales-Hub_d3yl25w6y3Y/Data-Quality_sueAz#_luZ9c).Does someone know if this is possible, and if so, how? If not this would be a nice idea though! Kind regards,Devin

Hi,I normally use Custom transformation (CASE statements) when I need to populate a field based on multiple conditions, in this case multiple “OR” conditions (Logical disjunction) . It was brought to my attention not to use CASE statements in Custom transformations. So I have below an example using a fixed value transformation but I want to add two conditions to populate only when it is ‘FL’ or ‘OL’. When I add these conditions the conditions are on separate lines but still TX makes an “AND” logic of it so instead of (like ‘FL%’ or like ‘OL%’) it translate it to (like ‘FL%’ & like ‘OL%’). How can I get the “OR” instead of “AND”?   

Hello,I have a history enabled table with a lookup field. The lookup field is marked as type I (update record). However, the record is only updated with the new values in the table from which the lookup field is retrieved, if the record is available in the raw variant of the history table. If the record has been updated in the mean time, the lookup field is never triggered to be updated.Is there a way to have the lookup fields be updated in a history enable table without the record being present in the raw table?I have currently created a work around by not truncating the raw table, but this causes an unnecessary increase in the size of the raw table and produces many primary key violation errors.Looking forward to your answer!

I need to make a field transformation such that if the field value starts with “DOK”, then I want to replace the value with “1001”. I tried to set up this custom field transformation using something along the lines of CASE WHEN …, but didn’t manage to get this to work. How would I write this code in TX?

We have a historic table and we use Dw_id as an unique indentifier. But when we update the record the DW_Id will stay the same, so we have duplicate values in the table on that field then. the original DW_id field is changing but the transformantion field isn’t.  Because we work with custom data aswell that we want to have there own value we work with the custom transformation. I will  provide some screens from the issue:   Can you provide me a way so the field eenheidssleutel will be unique over time and is equal to the DW

Anyone know in what TX repo table I can find the field and value for “Guard on deployement” and “Guard on execution?I know where the setting is in the UI, but need to look up the value in the repo db.

Hi can you add a diagram or something like that to understand when and where that scripts are executed? 

v20.10.35.64A while ago I was looking for a solution on how to conditionally fill down/flash fill values with certain criteria. Unfortunately the answers in this ticket didn't do the trick for me, as I kept getting NULL values with all solutions I've tried. How can I conditionally fill down/flash fill NULL values with previous values based on certain criteria? | Community (timextender.com)Now I seem to get it working with a Self Lookup view and a Lookup Field (Top first-non value). However, I have to repeat this process for almost 10 columns. The loading of this table went from 06:00 to 30:00 minutes. The lookup field looks as follows. What it does is:Look if column Route Bron is empty, If Route Bron is not empty, just show Route Bron.

In fact tables we are using subrogate keys from the dimension tables using supernatural keys.The relationship between the fact table and the dimension table it’s using the bussines key and getting the subrogate key from the dimension table into fact table. But some times it doesn’t exists, and I’m using -1 to put that value.What it’s the best way to include the unknow member (-1) in the dimension table?

In Custom data insert i have one field which is varbinary(8000) data type. When i try to insert a column i get an error: The given value of type String from the data source cannot be converted to type varbinary of the specified target column.Failed to convert parameter value from a String to a Byte[].Invalid cast from 'System.String' to 'System.Byte[]'.Details:Invalid cast from 'System.String' to 'System.Byte[]'.Module: mscorlibSystem.InvalidCastExceptionat System.Convert.DefaultToType(IConvertible value, Type targetType, IFormatProvider provider)at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)Failed to convert parameter value from a String to a Byte[].Module: System.DataSystem.InvalidCastExceptionat System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaTyp

Hi community,A customer is using a specific role that is allowed to select data from specific views in SQL. When one of these views are deployed, the Object Security Setup  isn't deployed. The Database Role/Security Settings needs to be redeployed manually.Is it possible to automatically deploy the Object Security Setup, if the a view/table is deployed that is using security settings?  

I see this advanced menu option in all the tables but I don’t know how can it be used. 

Hi,I am working on a column that evaluates a date against GetDate() and returns a 1 or 0. The table can be loaded incrementally. I assumed that if I turned on "Keep field values up-to-date", this column would always be recalculated at loadtime, but it seems that this is not true.Is there a way to make these two work together or do I have to give up on incremental loading?Thanks

Hey,I face this issue quite frequently and have not found out how to solve it yet so I’m hoping someone on here can tell me how to do this.I have a REST data source in which I make a selection of columns. I drag that table into the DSA layer, rename some columns and voila, I have my table. Now I found out that I need some additional columns from the data source so I go into my ODX and in the table selection I include those columns as well. The transfer is succesful and I have my data with additional columns. Now here comes my problem.I want to sync my table with the data source and add those additional columns to my existing table. When I right-click-drag the table into the DSA layer I can sync, smart sync and sync only with existing fields. The first options adds all columns to the bottom of my table (including the ones I already have). The other two options only sync the columns that I already have…Hope that someone can help me out as I face this more o

Hi TX Community!I get data from an external-database everyday.Since we have an aim of incremental-loading the data that we extract, we are doing some query-tables from the datasource to create a “incremental-load”-key.In that matter we are experiencing two issues:1- TX cannot read the date-formats that are extracted from the database. This is the format that we get out: Of course, we can right-click on the field and edit the datatype, but that we would have to do everytime we syncronize the datasource because everytime we syncronize the datasource all the date-fields are back to the “unknown”-format and therefore we would have to right-click on each field and edit the data-type. We’ve tried to use the “Data type overrides” but it doesnt seem that we can convert from an “unknown

Hi Friends, I contact you because I have to migrate a Dim Table with SCD 2 from the Old SQL DW to TX and after that set the TX Dim to the following working, Is there any process to do that? Thanks  for your help Ignacio

HelloI currently have a column with the following file name in one of my tables. C:\Users\John\OneDrive - Sales Solutions\Desktop\TimeXtender\TMX-DataSamples\RLZ\BESTERS - BESTERS POINT - BESTERS IND_DEC 22_101602_0.txtIs there a way to remove the path name in the column to only have the file name?I have a file for each month and have merged the files so all the data is in one table. For example, remove this piece: C:\Users\John\OneDrive - Sales Solutions\Desktop\TimeXtender\TMX-DataSamples\RLZ\and only show this piece:BESTERS - BESTERS POINT - BESTERS IND_DEC 22 as the file name.I also need to have the Month Year (DEC 22) of each file copied into a new column called “Date”. Is this at all possible?Thank you 

How can I solve extension _R tables failing to create? I am working on the old project Now.This project was copied from another project repository.Environment: SandboxVersion: 20.10.36.64Data Source: Business unitProvider: SQL Server Data SourceThe connection with the Data source is good and synchronized well.When I am deploying the tables in ODX storage getting an Error.An error occurred during create a table. See exception details for the failing object: Create failed for Table 'BSA.BSA_dbo_Inspection_R'.An exception occurred while executing a Transact-SQL statement or batch.The specified schema name "BSA" either does not exist or you do not have permission to use it.   

Did anyone try to connect azure sql database using Azure AD Integrated Authentication? I notice MFA was missing as an Auth type but AD Integrated was failing with error. Let me know if anyone resolved this error.    One or more errors occurred.Could not discover endpoint for Integrate Windows Authentication. Check your ADFS settings. It should support Integrate Widows Authentication for WS-Trust 1.3 or WS-Trust 2005. Details: Could not discover endpoint for Integrate Windows Authentication. Check your ADFS settings. It should support Integrate Widows Authentication for WS-Trust 1.3 or WS-Trust 2005.

Hi,I can’t find a way to update my table with the new field [Color] that has been added to the view the table insert is based on. Any suggestions?BRAnders