Skip to main content
Announcements
Meet the 2024 Qlik Luminaries and Partner Ambassadors! READ ON!
cancel
Showing results for 
Search instead for 
Did you mean: 
dmohanty
Partner - Specialist
Partner - Specialist

Load the Dates matching the Month Name in Variable

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.

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

6 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this

LOAD

*

RESIDENT TableName

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

Hope this helps you.

Regards,

Jagan.

dmohanty
Partner - Specialist
Partner - Specialist
Author

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
Luminary Alumni
Luminary Alumni

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
Partner - Specialist
Partner - Specialist
Author

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
Luminary Alumni
Luminary Alumni

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
Partner - Specialist
Partner - Specialist
Author

Thank you for the painstaking efforts and help.

Will try on this!