RE:pulling attribute by "created date"

Anne,

This is possible but  if you have more than one value per constituent for this specific Attribute you will get duplicate rows in your output.

So in tr_query_element add the following:

Data Select = !.create_dt

Data From = vxs_cust_keyword

Data Where = keyword_no = X

(where X represents the numerical value of your Attribute as found in t_keyword)

Dale

Thank you Dale. That worked! 

What if we would like to do the same from the List Manager?

Thanks much!

I tried to do this a couple years ago. It didn't behave well. Looking back, I think it was because there are some default views that deal with keywords (which attributes are one kind of.) It kept to the behavior of other attributes that dealt with keywords.

I think a way to do it would be to create a view of tx_cust_keyword and build the list elements off the view instead of the table.

Just a thought.

As Brian mentioned, this is a bit more tricky for a number of reasons.

The problem here is that when you set up an attribute in t_keyword where the detail tbl is TX_CUST_KEYWORD list manager will want to add the new keyword_no to the SQL it creates - even if you specify "list only" under Use For List.

If you use VXS_CUST_KEYWORD as the detail tbl you can prevent this but when you add another Attribute criteria in List Manager your two criteria will not play well together as the SQL created is looking at both a table and a view.

One solution that I can think of is to hard code the attribute's keyword_no into the new "create date" criteria so that this criteria will only give you those constituents who have this specific attribute AND the create date(s) you specify. Its not ideal but should give you what you want.

Here's how you can do that:

in T_KEYWORD add a row with the following values:

Description: Attribute Create dt for X (replace X with the name of your attribute)

Data Type: Date

Edit Mask: -1

Detail Tbl: VXS_CUST_KEYWORD

Detail Col: !.keyword_no = X and !.Create_dt (replace X with the keyword no of your attribute as found in t_keyword)

Category: Attributes

Use For List: List Only

Thank you Dale.

I tried this morning, the criteria is created under the List Manager,  however, when I generate the list, it returns with 0 members.  I have double checked the value in T_KEYWORD according to what you have gave me.  We currently only have one Create_dt value for this specific attribute and about 20,000 people with this attribute in different attribute value.

Thank you for taking another look at this!!

Anne,

I'm not sure if I can help without actually looking at your data and setup.  The first thing I would check is that under Detail Col: !.keyword_no =  has the correct keyword_no for the attribute in question.

Also when you set up your criteria in list manager, you should just have one criteria and that would be the new one you set up with a date range or date specified, don't add anything else and see if that works.

Dale

Thank you Dale. I really appreciated you are taking your time helping us out on this one..

I have created a screencast, if you do mind taking a look when you get a chance. Maybe you could let me know if I missed a step or made mistake somewhere. Much appreciated!!

http://screencast.com/t/4EaGtIh3ff0

-Anne

Anne,

Your setup is correct, the problem is actually with the dates - the way the create date is stored in that view, it is stored as datetime.  Sorry, I should have tested that.

You can do one of two things.  Leave your setup as is and when you specify your date in list manager do a range.  For example 06/08/2011 to 06/09/2011 will give you all records where the create date is 6/8/2011.

Or you can change the value in the t_keyword under the detail col so it is:

!.keyword_no =325 and convert (varchar, !.Create_dt ,111)

This works as long as you enter the date as mm/dd/yyy in list manager.

Dale

It works!! Thank you so much Dale.

This will help us save so much time from generating thousands of excel sheets and weaving through all the data before we get the result we want!

FYI: I tried both method that you mentioned. The first one, by keeping the same setting but using date range in the List Manager, it still generated 0 data for me but by change the setting in the T_KEYWORD, it works!!

-Anne:)