topic First Occurrence in Management & Governance

First Occurrence

Max75 — Mon, 12 Sep 2022 12:03:27 GMT

I have a need to create a field in a chart that shows me the FIRST date in 'YYYY-mm' format ('document last update') where the 'last validation' column is valued with B or C or F.
In the attached data for contract number 100 I would expect 2022-02, while for 120 I should have 2022-02.

Thank you to those who can support me.

contract number	document last update	condition text	last_validation
100	11/02/2022	Fatt_1	A
100	11/02/2022	Fatt_2	A
100	11/02/2022	Fatt_3	B
100	15/03/2022	Fatt_4	A
100	15/03/2022	Fatt_5	A
100	15/03/2022	Fatt_6	B
100	12/03/2022	Fatt_7	C
100	11/02/2022	Fatt_8	B
100	20/04/2022	Fatt_9	B
100	11/02/2022	Fatt_10	A
100	18/02/2022	Fatt_11	C
100	11/02/2022	Fatt_12	A
120	11/02/2022	Fatt_1	A
120	16/02/2022	Fatt_2	B
120	11/02/2022	Fatt_3	A
120	11/02/2022	Fatt_4	A
120	16/02/2022	Fatt_5	B
120	11/03/2022	Fatt_6	C
120	31/12/9999	Fatt_7	B
120	31/12/9999	Fatt_8	B
120	31/12/9999	Fatt_9	A
120	31/12/9999	Fatt_10	C
120	11/02/2022	Fatt_11	A

Re: First Occurrence

Zapparoli — Mon, 12 Sep 2022 12:56:03 GMT

Hey Max,

In the script i used Min() function on your date value with a Group By clause, so this way i can get only the first date in each contract while using the Date() Function to format it.

For your conditions I used a Wildmatch Function to filter them. See code below:

Temp: LOAD "contract number", "document last update", "condition text", last_validation FROM [lib://FolderA/teste.xlsx] (ooxml, embedded labels, table is Planilha1); NewTable: LOAD "contract number", Date(MIN("document last update"),'YYYY-MM') as Date RESIDENT Temp Where (WildMatch(last_validation, 'B', 'C' ,'F')) Group by "contract number"; DROP TABLE Temp;

The result is:

Now, you may want this field on your Fact table, if so, you can simply use a Join Statement with contract number as a key between them.

Please, let me know if it helped.

Re: First Occurrence

Max75 — Mon, 12 Sep 2022 13:11:41 GMT

Hi Matheus thanks for the support. I definitely didn't express myself well, unfortunately I don't have access to scripts, so I have to create the field in set analysis

Re: First Occurrence

Zapparoli — Mon, 12 Sep 2022 14:57:45 GMT

Max,

In this case you can use the AGGR() Function.

Try this:

Date( Min({< last_validation={'B', 'C', 'F'}>} Aggr( Min("document last update") ,[contract number]) ),'YYYY-MM')

This should give you the same result, I would not recommend using AGGR in large datasets, as it can slow down your application, but it solves the problem 🙂

Re: First Occurrence

Max75 — Fri, 23 Sep 2022 09:17:23 GMT

Thanks for the support!