Load the Dates matching the Month Name in Variable

[dmohanty]

Hi All,

I have a Variable with value  = 01/01/2015;

Now, I have a table with DATE field that has multiple dates.

I want to load ONLY the fields into a new Table from the existing table (using Resident) where the Month Name and Year Name should match that in the variable.

In this case, the new table should have all the dates that belong to January 2015 (January 2013, January 2014 etc should be excluded).

Please help.

[jagan]

Hi,

Try like this

LOAD

*

RESIDENT TableName

WHERE MonthName($(vDateVariableName)) = MonthName(DateFieldName);

Hope this helps you.

Regards,

Jagan.

[dmohanty]

Hi Jagan,

Me too tried in the same before as well, but don't know it didn't worked.

Below script:

Let vNextMonthDate = '01/01/2015' ;

FACT:

LOAD

Distinct ROW_UPDATE_STP

resident TEMP_FACT

where MonthName($(vNextMonthDate)) = MonthName(ROW_UPDATE_STP);

Infact, I am trying to work on a scenario described in this link below. Could you please help????

http://community.qlik.com/message/696882#696882

Regards!

[jagan]

Hi,

I think the date format is the issue.

FACT:

LOAD

Distinct ROW_UPDATE_STP

resident TEMP_FACT

where MonthName(MonthStart(Date#($(vNextMonthDate), 'MM/DD/YYYY'))) = MonthName(MonthStart(ROW_UPDATE_STP));

Hope this helps you.

Regards,

Jagan.

[dmohanty]

I am still trying some other methods as well,, above one didn't worked too

In fact the record in the ROW_UPDATE_STP field is in this format : 1/31/2014 6:12:31 AM.

could you please help more?

[jagan · Accepted solution]

Hi,

Try this sample script

LET vMonthName = Text(MonthName(MonthStart(Date#('01/01/2015', 'MM/DD/YYYY'))));

Data:

LOAD

*,

Text(MonthName(MonthStart(Date))) AS MonthName

INLINE [

Date

1/31/2015 6:12:31 AM

2/28/2015 6:12:31 AM ];

FinalData:

NoConcatenate

LOAD

*

RESIDENT Data

WHERE MonthName = '$(vMonthName)';

DROP TABLE Data;

Regards,

Jagan.

[dmohanty]

Thank you for the painstaking efforts and help.

Will try on this!