How to modify month

[#1]

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

[Anonymous]

Depending on your format and data you could try usinging:

Load Month,

          Year,  

From  ......

Where Month <= Month(today());

[Anonymous]

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

[Sokkorn]

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

[#5]

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

[#6]

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.-