Data in where clause smaller than certain other date

[Anonymous]

Hello,

I'm having the following script:

LastContactDate:
LOAD FirstSortedValue(ActivityDate,-ActivityDate) as LastContactUntilEnd2016,
[Account Name]
Resident ActivityData
Where Date(ActivityDate) < Date(makedate(2017,1,1))
Group by [Account Name];

In the where clause, I want to make sure that I only include rows in the load that are smaller than Jan 1st 2017.

Although there is data in 2016, it shows me nothing. When I remove the where clause, it just gives me the last contact date so it looks to me that the rest of the script should be fine.

Any idea on how to approach this?

Kind regards,

Christophe

[Anonymous]

No problem Kushal.

In the meantime I did the following.

I removed all 2017 data from the "resident table" so that my last possible date in the base table stops at end 2016.

Then I changed the load script to:

LastContactDate:
LOAD FirstSortedValue(ActivityDate,-ActivityDate) as LastContactUntilEnd2016,
[Account Name]
Resident ActivityData2016
Group by [Account Name];

And now I'm even more confused... for some it works, for others it doesn't. Although both have "hits"...

Struggeling narrowing down the problem...

For the sample dataset provided above, it doesn't work.

For this one it does:

Date	Account Name
11/05/17	Other Account
2/05/17	Other Account
2/05/17	Other Account
2/05/17	Other Account
2/05/17	Other Account
2/05/17	Other Account
6/04/17	Other Account
6/04/17	Other Account
6/04/17	Other Account
6/04/17	Other Account
6/04/17	Other Account
6/04/17	Other Account
6/04/17	Other Account
1/04/17	Other Account
23/03/17	Other Account
23/03/17	Other Account
23/03/17	Other Account
8/11/16	Other Account
26/10/16	Other Account
19/10/16	Other Account

[Anonymous]

Thank you so much!

What I found out in the meantime is that the issue occurs with the accounts that have the multiple lines of the same date as last interaction date.

I thought I'd add a distinct, but that did not work:

LastContactDate:
LOAD distinct FirstSortedValue(ActivityDate,-ActivityDate) as LastContactUntilEnd2016,
[Account Name]
Resident ActivityData2016
Group by [Account Name];

So I think the issue boils down to avoiding the double entries in the resident load?

You guys are great btw, thanks all for the support already!!

[Anil_Babu_Samineni]

Awesome !!! So, If you found answer or any helpful from the thread you can flag any one of the Answer is correct as you feel. If you found yourself you can flag your answer as correct

[Kushal_Chawda]

try to load full data and use FirstSortedValue on front charts instead in script

LastContactDate:

noconcatenate
LOAD *,
Resident ActivityData
Where floor(Date(ActivityDate)) < floor(Date(makedate(2017,1,1))

Drop table ActivityData;

[Anonymous]

Thanks for the suggestion; However I would like to end my load script with a STORE to CSV, so if possible at all, I'd like to work with the script approach. Or would that be impossible?

[Kushal_Chawda]

it will be better to share sample data file with script