Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have two columns in my source file and they are in different format. The source file contains more than 12 months of data and I need to filter out these not within last 12 months of the cycle date. In the sample below, it should filter out first 3 rows and the bottom 1 row in my backend script. I only need the bolded rows to be included in my data. Can anyone let me know how to get it done? Thank you!
cycle date | YEAR-MONTH |
2023-02-22 | 2021-11 |
2023-02-22 | 2021-12 |
2023-02-22 | 2022-01 |
2023-02-22 | 2022-02 |
2023-02-22 | 2022-03 |
2023-02-22 | 2022-04 |
2023-02-22 | 2022-05 |
2023-02-22 | 2022-06 |
2023-02-22 | 2022-07 |
2023-02-22 | 2022-08 |
2023-02-22 | 2022-09 |
2023-02-22 | 2022-10 |
2023-02-22 | 2022-11 |
2023-02-22 | 2022-12 |
2023-02-22 | 2023-01 |
2023-02-22 | 2023-02 |
MainTable:
Load * from .....(your source from where you are getting that above table);
noconcatenate
A:
load *, num(monthstart(Date(Date#(YEAR-MONTH,'YYYY-MM'),'YYYY-MM-DD'))) as FilterDate
resident MainTable;
noconcatenate
B:
Load * resident A
where FilterDate<num(monthstart(Today())) and FilterDate>=num(addmonths(Monthstart(Today()),-12));
Drop Table MainTable, A;
If it works please accept it as a solution.
MainTable:
Load * from .....(your source from where you are getting that above table);
noconcatenate
A:
load *, num(monthstart(Date(Date#(YEAR-MONTH,'YYYY-MM'),'YYYY-MM-DD'))) as FilterDate
resident MainTable;
noconcatenate
B:
Load * resident A
where FilterDate<num(monthstart(Today())) and FilterDate>=num(addmonths(Monthstart(Today()),-12));
Drop Table MainTable, A;
If it works please accept it as a solution.
Thank you, it worked very well!