topic Load in script YearMonth YYYYMM less than today() in QlikView

Load in script YearMonth YYYYMM less than today()

Wed, 25 Nov 2020 16:16:04 GMT

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?

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

kaushiknsolanki — Wed, 09 Mar 2016 08:21:03 GMT

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

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

Digvijay_Singh — Wed, 09 Mar 2016 08:21:19 GMT

Something like Where Period < Monthstart(today())

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

sunny_talwar — Wed, 09 Mar 2016 08:25:20 GMT

I think this should work:

Where Period <= Today();

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

MayilVahanan — Wed, 09 Mar 2016 08:30:42 GMT

Try like this

Load * From yourtable

Where Period <= Today();

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

tamilarasu — Wed, 09 Mar 2016 08:47:25 GMT

Hi,

Try like,

Load * From Table

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

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

Wed, 09 Mar 2016 09:15:03 GMT

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?

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

sunny_talwar — Wed, 09 Mar 2016 09:17:46 GMT

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

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

PradeepReddy — Wed, 09 Mar 2016 09:22:52 GMT

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

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

Wed, 09 Mar 2016 09:25:27 GMT

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

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

sunny_talwar — Wed, 09 Mar 2016 09:29:11 GMT

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

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