topic Re: Date Filtering Logic Failing in Fact Table Script in Data Quality

Date Filtering Logic Failing in Fact Table Script

migueldfr_23 — Thu, 07 Aug 2025 10:52:44 GMT

Hello everyone,

I'm struggling with a script where the keys are not working as expected.

I have a fact table with only three fields (although I have more in total, but created this in order to simplify), and later I create a final table with all the fields I need, properly transformed.

After that, I need to create a table with some filters and specific requirements.

rds_telemonitoring_alarm: LOAD id as alarm_id, account_number, IF(date(FLOOR(datetime_edp_preprocessing)) >= Today() - 30, 'Lower 30 days', 'Greater 30 days') as has_recent_alarm_30_days //NEW2 FROM [lib://QVD.01.Stage/FSM/tlm_historic_alarm.qvd] (qvd); //--------------------------------------------------------- rds_telemonitoring_alarm_final: NOCONCATENATE LOAD alarm_id, account_number, has_recent_alarm_30_days RESIDENT rds_telemonitoring_alarm; DROP TABLE rds_telemonitoring_alarm; //--------------------------------------------------------- accounts_active_with_alarms_last_30_days: LOAD account_number, // Identificador único de la cuenta // Count(alarm_id) as num_alarms, IF( (Max(Date) >= Today() - 30), 'Lower 30 days', 'Greater 30 days') as has_recent_alarms RESIDENT rds_telemonitoring_alarm_final // Fuente de datos: tabla cargada con las alarmas WHERE // Alarmas ocurridas en los últimos 30 días telemonitoring_contract_active_flag__c = 1 // Cuenta con contrato TLS activo AND (tls_contract_end_date__c > Today() OR IsNull(tls_contract_end_date__c)) // Contrato sin fecha de fin o aún vigente GROUP BY account_number;

However, when I select "Has Recent Alarms" > "Lower 30 Days", it doesn't return alarms from the last 30 days. Instead, it shows alarms with a datetime older than 30 days.

With the ERD,

Hope anyone can help me out

Thank you

Re: Date Filtering Logic Failing in Fact Table Script

marcus_sommer — Thu, 07 Aug 2025 12:16:15 GMT

Make sure that your timestamp is really a timestamp and not a string or containing any other invalid data.

Personally I wouldn't tend to apply such expensive approach with an intermediate resident table and aggregating results in an extra dimension table else creating an appropriate cluster-information directly within the facts, for example with something like:

floor((MyDate - today()) / 30) as Alarm30Cluster

Re: Date Filtering Logic Failing in Fact Table Script

savioricardog — Thu, 07 Aug 2025 12:21:43 GMT

Hi, @migueldfr_23 Could you send a small sample of your dataset for testing (A small sample of rows is enough.)?
Also, could you send the full creation script for these three tables?

Re: Date Filtering Logic Failing in Fact Table Script

migueldfr_23 — Fri, 08 Aug 2025 10:00:23 GMT

Hello @marcus_sommer,

Thank you for prompt response

the table has been implemented with this field which is Date.

rds_telemonitoring_alarm: LOAD id as alarm_id, account_number, date(FLOOR(datetime_edp_preprocessing)) AS Date, IF(date(FLOOR(datetime_edp_preprocessing)) >= Today() - 30, 'Lower 30 days', 'Greater 30 days') as has_recent_alarm_30_days //NEW2 FROM [lib://QVD.01.Stage/FSM/tlm_historic_alarm.qvd] (qvd);

Thank you

Re: Date Filtering Logic Failing in Fact Table Script

migueldfr_23 — Fri, 08 Aug 2025 10:15:43 GMT

Hello @savioricardog

Thank you for such a prompt response.

Here you have a sample of rows .xlxs, and also I have sent out all the script created.

Let me know if you need further samples.

Thank you

Re: Date Filtering Logic Failing in Fact Table Script

migueldfr_23 — Mon, 11 Aug 2025 14:20:55 GMT

Here is the example with 10 rows.

Thanks

Re: Date Filtering Logic Failing in Fact Table Script

savioricardog — Tue, 12 Aug 2025 01:20:55 GMT

Hi @migueldfr_23

I tried the code above and it works... (I guess you’re having issues because the join between the table rds_telemonitoring_alarm and accounts_active_with_alarms_last_30_days is only based on account_number, and it can have duplicate values)

Try this:

TEST:

LOAD

account_number &'-'& "Date" AS DateAccountKey,

"Date",

account_number,

telemonitoring_contract_active_flag__c,

tls_contract_end_date__c

FROM [lib://Dados/fd0b221e-5bae-42f9-b3a9-2fbb5573026a.xlsx]

(ooxml, embedded labels, table is Sheet1);

LEFT JOIN (TEST)

LOAD

SubField(DateAccountKey, '-', 1) &'-'& MAX(SubField(DateAccountKey, '-', 2)) AS DateAccountKey,

SubField(DateAccountKey, '-', 1) AS account_number_max

RESIDENT TEST

GROUP BY SubField(DateAccountKey, '-', 1);

accounts_active_with_alarms_last_30_days:

LOAD

account_number_max

, IF( (Max(Date) >= Today() - 30), 'Lower 30 days', 'Greater 30 days') as has_recent_alarms

RESIDENT TEST

WHERE

telemonitoring_contract_active_flag__c = 1

AND (tls_contract_end_date__c > Today() OR IsNull(tls_contract_end_date__c))

GROUP BY

account_number_max

;

Re: Date Filtering Logic Failing in Fact Table Script

migueldfr_23 — Tue, 12 Aug 2025 10:03:00 GMT

Hello

Could you explain me why you are doing this middle table

LEFT JOIN (TEST)
LOAD
SubField(DateAccountKey, '-', 1) &'-'& MAX(SubField(DateAccountKey, '-', 2)) AS DateAccountKey,
SubField(DateAccountKey, '-', 1) AS account_number_max
RESIDENT TEST
GROUP BY SubField(DateAccountKey, '-', 1);

Thanks

Re: Date Filtering Logic Failing in Fact Table Script

savioricardog — Tue, 12 Aug 2025 11:24:08 GMT

Hi @migueldfr_23,
I used this middle table to create the field "account_number_max" and at the same time, build a consistent join key that I could use between accounts_active_with_alarms_last_30_days and TEST.

The issue I identified in your case is that if you join only on "account_number", there can be multiple matching rows for the same account (because it has multiple dates). When that happens, a join multiplies the results and can filter or distort the data unexpectedly.

By first calculating the latest date per account and creating a composite key (account_number + latest date), I ensure the join happens only on that specific latest record for each account. This avoids the one-to-many join problem and keeps the results consistent.

Re: Date Filtering Logic Failing in Fact Table Script

migueldfr_23 — Wed, 13 Aug 2025 08:43:21 GMT

HI @savioricardog

Thanks for such a good response, but I am still having issue with the order of the script.

From my knowledge, I will set what I had before ...

First 10

rds_telemonitoring_alarm:

LOAD id as alarm_id,

source_flow,

source_technology,

date(FLOOR(datetime_edp_preprocessing)) AS Date,

datetime_edp_preprocessing,

datetime_edp_processing,

datetime_start,

datetime_close,

Interval(datetime_close - datetime_edp_processing, 'hh:mm:ss') as minutes_start_to_close,

interval(datetime_close - datetime_edp_preprocessing, 'hh:mm:ss') as minutes_recepcion_to_close,

account_number,

original_message,

alarm_text,

state as state_id,

original_priority,

original_store,

alarm_type_id ,

asset,

serial_number,

original_equipment,

system_id,

date(datetime_end) AS datetime_end,

sf_ticket_number,

position,

original_technology,

1 as Contador,

alarm_note, //NEW

process_note, //NEW

agent_in_charge, //NEW

agent_closed, //NEW

suspension_end_datetime, //NEW

last_modification_date, //NEW

closure_reason, //NEW2

last_modification , //NEW2, //NEW2,

IF(date(FLOOR(datetime_edp_preprocessing)) >= Today() - 30, 'Lower 30 days', 'Greater 30 days') as has_recent_alarm_30_days //NEW2

FROM [lib://QVD.01.Stage/FSM/tlm_historic_alarm.qvd]

(qvd);

LEFT JOIN

LOAD

accountnumber as account_number,

id AS account_id,

retailer_description__c,

fsl_servicecallterritory__c as service_territory_id,

shippingcountry,

banner_description__c,

billingcity,

billingcountry,

billingcity & ',' & billingcountry as location_iso,

store_management__c,

name,

billingstreet,

mycountry__c,

"maintenancecontractactiveflag__c",

"telemonitoring_contract_active_flag__c",

tls_contract_end_date__c

FROM [lib://QVD.01.Stage/FSM/crm_account.qvd]

(qvd);

rds_telemonitoring_alarm_final:

NOCONCATENATE

LOAD

alarm_id,

source_flow,

source_technology,

Date,

datetime_edp_preprocessing,

datetime_edp_processing,

datetime_start,

datetime_close,

minutes_start_to_close,

minutes_recepcion_to_close,

account_number,

// account_number, // as account_number_tlm_info,

// account_number as account_number_Test_di_vita ,

original_message,

alarm_text,

state_id,

original_priority,

original_store,

alarm_type_id ,

asset,

serial_number,

original_equipment,

system_id,

datetime_end,

sf_ticket_number,

position,

original_technology,

Contador,

alarm_note, //NEW

process_note, //NEW

agent_in_charge, //NEW

agent_closed, //NEW

suspension_end_datetime, //NEW

last_modification_date, //NEW

closure_reason, //NEW2

last_modification, //NEW2

// If(len(Status)>0, Status, 'Unknown') as Status,

// description_alarm_type ,

// language_alarm_type,

// suggestions_alarm_type,

// type_severity,

account_id,

account_id as relatedrecordid, //key

account_id as account_id_cat, //key

retailer_description__c,

// service_territory_id,

shippingcountry,

banner_description__c,

billingcity,

If(account_number = '1000000', 'Germany',

If(Len(billingcountry) > 0, billingcountry, 'Unknown')

) as billingcountry,

If(account_number = '1000000', 'Germany',

If(Len(billingcountry) > 0, billingcountry, 'Unknown')

) as REDUCTION,

location_iso,

store_management__c,

If(len(name)>0, name, 'Unknown') as name,

billingstreet,

mycountry__c,

// service_territory_zipcode,

// service_territory_city,

// fsl_assigneddepartments__c,

// service_territory_country,

// service_territory_countrycode,

// isactive,

// service_territory_street,

// service_territory_name,

// service_territory_state,

maintenancecontractactiveflag__c,

telemonitoring_contract_active_flag__c,

tls_contract_end_date__c,

// If(Status = 'INACTIVE' and len(datetime_end)>0 , 'true', 'false') as "Is Okay Message",

IF ( Date < Today() - 6,'False', 'True') AS "Alarms in the last 7 days",

has_recent_alarm_30_days

// shift

RESIDENT rds_telemonitoring_alarm;

accounts_active_with_alarms_last_30_days:

LOAD

account_number, // Identificador único de la cuenta

// Count(alarm_id) as num_alarms,

IF( (Max(Date) >= Today() - 30), 'Lower 30 days', 'Greater 30 days') as has_recent_alarms

RESIDENT rds_telemonitoring_alarm_final // Fuente de datos: tabla cargada con las alarmas

WHERE

// Alarmas ocurridas en los últimos 30 días

telemonitoring_contract_active_flag__c = 1 // Cuenta con contrato TLS activo

AND (tls_contract_end_date__c > Today() OR IsNull(tls_contract_end_date__c)) // Contrato sin fecha de fin o aún vigente

GROUP BY

account_number;

And where should I ingest this script you provide to me ?

TEST:

LOAD

account_number &'-'& "Date" AS DateAccountKey,

"Date",

account_number,

telemonitoring_contract_active_flag__c,

tls_contract_end_date__c

FROM [lib://Dados/fd0b221e-5bae-42f9-b3a9-2fbb5573026a.xlsx]

(ooxml, embedded labels, table is Sheet1);

LEFT JOIN (TEST)

LOAD

SubField(DateAccountKey, '-', 1) &'-'& MAX(SubField(DateAccountKey, '-', 2)) AS DateAccountKey,

SubField(DateAccountKey, '-', 1) AS account_number_max

RESIDENT TEST

GROUP BY SubField(DateAccountKey, '-', 1);

accounts_active_with_alarms_last_30_days:

LOAD

account_number_max

, IF( (Max(Date) >= Today() - 30), 'Lower 30 days', 'Greater 30 days') as has_recent_alarms

RESIDENT TEST

WHERE

telemonitoring_contract_active_flag__c = 1

AND (tls_contract_end_date__c > Today() OR IsNull(tls_contract_end_date__c))

GROUP BY

account_number_max

DROP TABLE rds_telemonitoring_alarm;

Re: Date Filtering Logic Failing in Fact Table Script

migueldfr_23 — Wed, 13 Aug 2025 10:32:06 GMT

Hello,

//------------------------------------------------------
// 1️⃣ Crear tabla temporal con DateAccountKey
//------------------------------------------------------
rds_telemonitoring_alarm_test:
NOCONCATENATE
LOAD
account_number & '@' & Date AS DateAccountKey,
Date,
account_number,
telemonitoring_contract_active_flag__c,
tls_contract_end_date__c
RESIDENT rds_telemonitoring_alarm_final;

//------------------------------------------------------
// 2️⃣ LEFT JOIN para traer la fecha máxima por account_number
//------------------------------------------------------
LEFT JOIN (rds_telemonitoring_alarm_test)
LOAD
SubField(DateAccountKey, '@', 1) & '@' & Max(SubField(DateAccountKey, '@', 2)) AS DateAccountKey,
SubField(DateAccountKey, '@', 1) AS account_number_max
RESIDENT rds_telemonitoring_alarm_test
GROUP BY SubField(DateAccountKey, '@', 1);

//------------------------------------------------------
// 3️⃣ Crear tabla final de cuentas activas con alarmas recientes
//------------------------------------------------------
accounts_active_with_alarms_last_30_days:
LOAD
account_number_max,
IF(Max(Date) >= Today() - 30, 'Lower 30 days', 'Greater 30 days') AS has_recent_alarms
RESIDENT rds_telemonitoring_alarm_test
WHERE telemonitoring_contract_active_flag__c = 1
AND (tls_contract_end_date__c > Today() OR IsNull(tls_contract_end_date__c))
GROUP BY account_number_max;

//------------------------------------------------------
// 4️⃣ Limpiar tablas temporales
//------------------------------------------------------
DROP TABLE rds_telemonitoring_alarm_final;

I do not know why this does not work properly.

Thank you

Re: Date Filtering Logic Failing in Fact Table Script

savioricardog — Wed, 13 Aug 2025 12:07:24 GMT

Hi @migueldfr_23, sorry for the issue, i really didn't notice that some values have "-"...

So I fix the issue like this:

TEST:

LOAD

account_number &'|'& num(floor("Date")) AS DateAccountKey,

"Date",

account_number,

telemonitoring_contract_active_flag__c,

tls_contract_end_date__c

FROM [lib://Dados/fd0b221e-5bae-42f9-b3a9-2fbb5573026a.xlsx]

(ooxml, embedded labels, table is Sheet1)

;

LEFT JOIN (TEST)

teste2:

LOAD

SubField(DateAccountKey, '|', 1)

&'|'&

Max(Num(floor(SubField(DateAccountKey, '|', 2)))) AS DateAccountKey,

SubField(DateAccountKey, '|', 1) AS account_number_max

RESIDENT TEST

GROUP BY SubField(DateAccountKey, '|', 1);

accounts_active_with_alarms_last_30_days:

LOAD

account_number_max

, IF( (Max(Date) >= Today() - 30), 'Lower 30 days', 'Greater 30 days') as has_recent_alarms

RESIDENT TEST

WHERE

telemonitoring_contract_active_flag__c = 1

AND (tls_contract_end_date__c > Today() OR IsNull(tls_contract_end_date__c))

GROUP BY

account_number_max

;

Try this, I think this helps you.