Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Load in script YearMonth YYYYMM less than today()

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?

Tags (1)
1 Solution

Accepted Solutions
Highlighted

Re: Load in script YearMonth YYYYMM less than today()

I see what you mean. Try this in that case:

Where Date#(Period,'YYYYMM') < MonthStart(Today());

View solution in original post

10 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Load in script YearMonth YYYYMM less than 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


Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted
Master III
Master III

Re: Load in script YearMonth YYYYMM less than today()

Something like Where Period < Monthstart(today())

Highlighted

Re: Load in script YearMonth YYYYMM less than today()

I think this should work:

Where Period <= Today();

Highlighted

Re: Load in script YearMonth YYYYMM less than today()

Hi

Try like this

Load * From yourtable

Where Period <= Today();

Re: Load in script YearMonth YYYYMM less than today()

Hi,

Try like,

Load * From Table

Where Period < Date(Today(),'YYYYMM');

Highlighted
Not applicable

Re: Load in script YearMonth YYYYMM less than today()

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?

Highlighted

Re: Load in script YearMonth YYYYMM less than today()

Why would it show 201604 and 201605? Those period are greater than Today(), right?

Highlighted
Specialist II
Specialist II

Re: Load in script YearMonth YYYYMM less than today()

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')

Highlighted
Not applicable

Re: Load in script YearMonth YYYYMM less than today()

Yes, that is OK, but why does it show 201603? 201603 is not less than today?