
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
First Occurrence
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 |
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 🙂
Check my Youtube Channel for more Qlik Content
https://www.youtube.com/@ZappaAnalytics

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Check my Youtube Channel for more Qlik Content
https://www.youtube.com/@ZappaAnalytics

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 🙂
Check my Youtube Channel for more Qlik Content
https://www.youtube.com/@ZappaAnalytics

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for the support!
