Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
got a Date field YYYYMM in a table, formatted like this in an earlier step/app:
Date(Date#(MyField,'YYYYMM'),'YYYYMM') AS Period
Now, in a second app, I will load this table (with the Period field), and use a where clause that should only load records where Period is less than todays YYYYMM. For example, now we have March 2015 (201503) and then I will only load records less than 201503.
How can I do that? Format and use today() somehow?
I see what you mean. Try this in that case:
Where Date#(Period,'YYYYMM') < MonthStart(Today());
Hi,
Are you saying that you are creating a new application and loading the same table, then you should do as below.
Load *
from xyz where Date(Date#(MyField,'YYYYMM'),'YYYYMM') < date(today(),'YYYYMM');
If you are saying that you are doing this in same app, where in first step have already converted this then you should do as below.
Load *
Resident (Tablename Which you have loaded earlier)
where Period <
date(today(),'YYYYMM');
Regards,
Kaushik Solanki
Something like Where Period < Monthstart(today())
I think this should work:
Where Period <= Today();
Hi
Try like this
Load * From yourtable
Where Period <= Today();
Hi,
Try like,
Load * From Table
Where Period < Date(Today(),'YYYYMM');
Hi,
have made an example (see attached file) which contains this code:
LOAD Item, Date(Date#(Period,'YYYYMM'),'YYYYMM') as Period
INLINE [
Item, Period
A, 201601
B, 201602
C, 201603
D, 201604
E, 201605
]
where Date(Date#(Period,'YYYYMM'),'YYYYMM') < date(today(),'YYYYMM');
The result still shows 201603 (but not 201604 and 201605).
What am I doing wrong?
Why would it show 201604 and 201605? Those period are greater than Today(), right?
if u are applying the format and filter in a single load statement, then try like this...
Load * from table
where Date(Date#(MyField,'YYYYMM'),'YYYYMM') <Date(today(),'YYYYMM')
Yes, that is OK, but why does it show 201603? 201603 is not less than today?