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 

[marcus_sommer]

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

[savioricardog]

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?

[migueldfr_23]

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 

[migueldfr_23]

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 

[migueldfr_23]

Here is the example with 10 rows.

Thanks

[savioricardog]

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 

;       

[migueldfr_23]

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

[savioricardog]

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.

[migueldfr_23]

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;