loading dates greater than specific value

[marikabi]

Hello guys,

I need an help in modifying the loading script.

What I am trying to do is to load only the data starting from 2016.

I have a column called [Period Name] where the dates are in this format : yyyy-mm-dd

For each record, the value in day is 01.

For example, for November 2016 I have : 2016-11-01

I have data also about 2015 and 2014.

How can I modify the script to load only the required data?

The piece of script for this part looks like:

LOAD Account, [Period Name] AS [MM/YY], [EURO NET], [Country] AS COUNTRY
FROM
(ooxml, embedded labels, table is Data)
WHERE (Account='522110') or (Account='522120') or (Account='522210') or (Account='522220') or (Account='522410') ;

Could you please help me?

Many thanks.

Marika

[eespiritu]

Hi,

LOAD Account, [Period Name] AS [MM/YY], [EURO NET], [Country] AS COUNTRY
FROM
(ooxml, embedded labels, table is Data)
WHERE (Account='522110') or (Account='522120') or (Account='522210') or (Account='522220') or (Account='522410')

and year([Period Name] ) = 2016 ;

Regards,

[sunny_talwar]

May be this:

LOAD Account, [Period Name] AS [MM/YY], [EURO NET], [Country] AS COUNTRY
FROM
(ooxml, embedded labels, table is Data)
WHERE Match(Account, '522110', '522120', '522210', '522220', '522410') and Year(Date#([Period Name], 'YYYY-MM-DD')) >= 2016;

or if Period Name is understood as date already, then this

LOAD Account, [Period Name] AS [MM/YY], [EURO NET], [Country] AS COUNTRY

FROM

(ooxml, embedded labels, table is Data)

WHERE Match(Account, '522110', '522120', '522210', '522220', '522410') and Year([Period Name]) >= 2016;

[marikabi]

Hi Enrique,

unfortunately it doesn't work

Should I also modify something in the first part of the script?

[marikabi]

Hi Sunny,

it doesn't work

Using both the codes I still have all the data..

Any other idea?

[sunny_talwar]

Can you run this:

LOAD Account,

          [Period Name] AS [MM/YY],

          Num([Period Name]) as NumDate,

          Year(Date#([Period Name], 'YYYY-MM-DD')) as Year1,

          Year([Period Name]) as Year2,

          [EURO NET],

          [Country] AS COUNTRY

FROM

(ooxml, embedded labels, table is Data)

WHERE Match(Account, '522110', '522120', '522210', '522220', '522410');

and share a table box with 4 fields in it

[MM/YY],

NumDate,

Year1,

Year2

[eespiritu]

Hi,

Probably your date field has not date format, then you should take only the sub string for the year, try this:

where left([Period Name],4) >= 2016

[MK9885]

If you've master Calendar already then give the varMinDate as 2016-01-01

Ex:

varMinDate = num(date(mid('2016-01-01',1,10 ),'YYYY-MM-DD'));

This can be used in Master Calendar and the dates will generate only from 2016 and data prior to this will not be available for the user.

[marikabi]

This is the table..

Perhaps I do have a problem with the format of the dates in the second excel?

[marikabi]

Hi,

still nothing..

I am not an expert of Qlik, but this is driving me crazy