Min value in date in script

lucasx15 — Fri, 04 Nov 2022 14:05:48 GMT

Hi everyone, I have a question for a while. I have the following situation. get the first accurate record date from the customer in my table, well, for that I loaded it first,
the table where I have all the records and to get the min of this date, create the following transformation:
The NR_CLIENT fields contain the unique code of each customer, the NR_sequence field contains a table primary key with the sequence of records and the dt_registry field the date of the record when the customer entered the system.

The problem is that it is marking all dates in the table as the first date.

MIN_REGIST_1: LOAD NR_CLIENT, NR_SEQUENCE, DT_REGISTRY RESIDENT CLIENT_TABLE; MIN_REGIST_2: LOAD NR_CLIENT, NR_SEQUENCE, TIMESTAMP(MIN(DT_REGISTRY)) AS DT_REGISTRY_MIN, 'S' AS FL_DT_REGISTRY_MIN RESIDENT MIN_REGIST_1 Group BY NR_CLIENT,NR_SEQUENCE; LEFT JOIN(CLIENTS) LOAD NR_SEQUENCE, DT_REGISTRY_MIN , FFL_DT_REGISTRY_MIN Resident MIN_REGIST_2; DROP TABLES MIN_REGIST_1,MIN_REGIST_2 ;

Re: Min value in date in script

maxgro — Fri, 04 Nov 2022 19:27:17 GMT

I think you should group by NR_CLIENT not by NR_SEQUENCE

// test data
MIN_REGIST_1:
LOAD
CEIL(RAND()*10) as NR_CLIENT,
ROWNO() as NR_SEQUENCE,
DATE(MAKEDATE(2020) + FLOOR(RAND()*365)) AS DT_REGISTRY
AutoGenerate 100;

// group by client and get the min by client; join by client and dt_registry to MIN_REGIST_1

LEFT JOIN (MIN_REGIST_1)
LOAD
NR_CLIENT,
MIN(DT_REGISTRY) AS DT_REGISTRY,
1 AS FL_DT_REGISTRY_MIN
RESIDENT MIN_REGIST_1
GROUP BY NR_CLIENT;

topic Re: Min value in date in script in Management & Governance

Min value in date in script

Re: Min value in date in script