Forum Discussion

Ivancito111's avatar
Ivancito111
Resolver I
4 years ago
Solved

Help with a LOOKUPVALUE

Hello, sorry to bother you and thank you for taking the time to read my problem.
Also excuse my English, I am using DeepL Translator.

Well let's start. Today I was asked to use LOOKUPVALUE to search for a specific data, but if it doesn't find a data, search with another formula. For this I use the following data.

 

LeadsV2

SuuborigeneMailDate

The Rock 1[email protected]23-12-2021 11:00:00
The Rock 2[email protected]22-12-2021 12:00:00
The Rock 3[email protected]23-12-2021 10:58:12
The Rock 4[email protected]02-12-2021 12:00:00
The Rock 5[email protected]03-12-2021 13:00:00
The Rock 6[email protected]12-12-2021 14:00:00
The Rock 7[email protected]13-12-2021 14:00:00
The Rock 8[email protected]14-12-2021 14:00:00
The Rock 9[email protected]15-12-2021 14:00:00
The Rock 10[email protected]16-12-2021 14:00:00
The Rock 11[email protected]17-12-2021 14:00:00

 

modelov2

SuborigenWelcome GUID

123La calle 123
125La calle 125
126La calle 126
128La calle 128
129La calle 129
131La calle 131
132La calle 132
133La calle 133
134La calle 134
135La calle 135
136La calle 136

 

TodoIntranet

lead_pilotcliente_mailOrigen Lead

123[email protected]La calle 123
124[email protected] 
125[email protected]La calle 125
126[email protected]La calle 126
127[email protected] 
128[email protected]La calle 128
129[email protected]La calle 129
130[email protected] 
131[email protected]La calle 131
132[email protected]La calle 132
133[email protected]La calle 133

 

In my attempt to do so, I logged in to get data from the table modelov2, and this is the code and the result

 

 

Origen Lead = 
var __suborigen = 
    LOOKUPVALUE( modelov2[Welcome GUID],
                'modelov2'[Suborigen],
                'TodoIntranet'[lead_pilot])

var __email = 
    LOOKUPVALUE('LeadsV2'[Suuborigen],
                'LeadsV2'[eMail],
                'TodoIntranet'[cliente_mail])


return __suborigen

 

 

The idea is that when the variable __suborigin is blank, it searches in the variable __email, but every time I try to test the variable __email I get the error that it has more than one data.

 

 

I understand the error, I checked in the LeadsV2 table and the data is repeated, but with different date, is there any way to take the first time, that is, the oldest date of the record.

 

As you can see there are 3 records with the same email, but I need the oldest one.
It should be noted that there are times when the difference is of hours, that is, they are the same day but one was made before the other.

 

Any kind of help would be important to me.

Thank you for everything.

Ivancito

4 Replies