topic How to modify month in QlikView

How to modify month

Tue, 08 May 2012 07:36:34 GMT

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

Re: How to modify month

Anonymous — Tue, 08 May 2012 08:26:34 GMT

Depending on your format and data you could try usinging:

Load Month,

Year,

From ......

Where Month <= Month(today());

Re: How to modify month

Anonymous — Tue, 08 May 2012 09:22:13 GMT

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

Re: How to modify month

Sokkorn — Tue, 08 May 2012 10:30:42 GMT

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

How to modify month

Tue, 08 May 2012 10:41:37 GMT

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

How to modify month

Tue, 08 May 2012 12:12:37 GMT

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