Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
My script contain year(2009,2010,2011,2012) and month field. I need a new field as month which should contain only the completed month of 2012.If the completed month for 2012 is jan, feb, march, april. Only these 4 months should be visible for other years too(2008,2009,2010).
For example the field should be
Year Month
2009 Jan
2010 Feb
2011 March
2012 April
Regards,
Gerard
Depending on your format and data you could try usinging:
Load Month,
Year,
From ......
Where Month <= Month(today());
Hi Gerard,
An other option is to determine what your last month is and store this in a variable.
you can use this variable in your script to only load the data with a month lower then that variabel.
Or you can use that variable in your expression and calculated dimensions
If your load scrip look fe like this:
Data:
LOAD Month as Month,
Year
FROM
testYM.xlsx
(ooxml, embedded labels, table is Blad1)
You can add
LET v_MaxMonth = (peek('Month' , -1 , 'Data'));
To determine the last loaded Month. (this only works when your load is in date order)
It mighty be usefull (needed) to add month numbers to your script, that makes it easier to count and use the variabel.
Let me know if this is helpfull.
Good luck,
Dennis.
PS if the current month is always the last month in your data you could also use:
LET v_MaxMonth = Month(Today())
Hi,
Try this one:
Create a KEY for link table
[Data]:
LOAD * INLINE [
KEY, Year, tmpMonths
M, 2009, Jan
M, 2010, Feb
M, 2011, March
M, 2012, April];
[MonthData]:
LOAD
KEY,
tmpMonths AS [Months]
Resident [Data];
DROP FIELD tmpMonths;
See sample attached file.
Regards,
Sokkorn
Thanks that was good. But how can i get a new month field with only completed months of 2012.
This may store the last month LET v_MaxMonth = (peek('Month' , -1 , 'Data'));. From this how can i get the remaining months in month field.
So that when i pull the month field for 2012 it should contain only jan, feb, march, april
You can try this:
Table:
LOAD Month,
Year
FROM table.qvd (qvd);
Left Join
LOAD Month,
Month2012
FROM table.qvd(qvd)
WHERE Year = 2012;
Regards.-