First Occurrence

[Max75]

Hi

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

[Zapparoli]

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.

[Max75]

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

[Zapparoli · Accepted solution]

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 🙂 

[Max75]

Thanks for the support!