Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi All,
thank for your support.
I don't know why, but the only way the year function worked was copying-pasting the dates as value.
This is crazy, but it worked
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,
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;
Hi Enrique,
unfortunately it doesn't work
Should I also modify something in the first part of the script?
Hi Sunny,
it doesn't work
Using both the codes I still have all the data..
Any other idea?
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
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
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.
This is the table..
Perhaps I do have a problem with the format of the dates in the second excel?
Hi,
still nothing..
I am not an expert of Qlik, but this is driving me crazy