Date Filtering Logic Failing in Fact Table Script

[migueldfr_23]

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 

[migueldfr_23]

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 

[savioricardog · Accepted solution]

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.