Lookup table using two lookup columns

[Olivera_Kalinic (Olivera Kalinić)]

Hi. Is there a way to use Lookup table activity with two lookup columns?

I have data table dt1 from which I have to use columns VALUTA and DATUM VALUTE (example below)

to find appropriate Rate (column from data table dt2 - example below)

[ashwin.ashok ((TheCodingTheory))]

Hi @Olivera_Kalinic ,

Would you be so kind as to explain which values you are looking up with?
I understand that you want to lookup based on two columns, but should it look up based on Rate(which column in dt1)?

It would be great if you could provide some sample files as well.

Kind Regards,
Ashwin A.K

[ppr (Peter Preuss)]

we can handle it as

• a filter case on 2 columns
• using LINQ (Join, Where, FindIndex Approach)
• creating a Lookup Dictionary where key is a concatenated value of VALUTA and DATUM VALUTE

Kindly note: the date is maybe to handle additional and we would also recommend to respecify the requirement as maybe dates are not matching

[Olivera_Kalinic (Olivera Kalinić)]

I have to return column Rates from dt2 where dt2.Dates = dt1.Datum valute and dt2.Currency = dt1.Valuta

DT1:

KORISNIK	INSTRUMENT PL	BROJ NALOGA	INO PARTNER	ZEMLJA INO PAR.	IZNOS	VALUTA	DATUM VALUTE	TROSAK S.BANKE	KONTRA RACUN	KREDIT PRIJAVA	REDNI BR	SIFRA OSNOVA	OPIS TRANSAKCIJE	IZNOS2	RACUN P/O	POSAO	ZNAK
00003008	1	144681133	HERBALIFE - SPECIAL PROGRAM - USD -, 800 WEST OLYMPIC BOULEVARD 406, U	US	115.56	USD	13.04.2021	10.00	2334		1	502	HERBALIFE EUROPE LTD - REMITTANCE P, AYMENT, /ROC/02241985600000904	115.56	1009	60	P
00003036	1	144682377	DAVID MORAN, 23 RODITIS DR OCEAN GROVE VIC 3226, Australia	AU	1642.43	EUR	14.04.2021		2334		1	502	T +38269430905 Gift anniversary	1642.43	1009	60	P
00003036	1	144744327	DAVID MORAN, 23 RODITIS DR OCEAN GROVE VIC 3226, AUSTRALIA	AU	1989.22	USD	14.04.2021	6.00	2334		1	502	T +38269374802 GIFT ANNIVERSARY	1989.22	1009	60	P
00003040	1	144905384	WORLDPAY AP LTD, 25 WALBROOK, GB/LONDON/EC4N 8AF	GB	557.22	USD	16.04.2021	10.00	2334		1	502	0GG1ALYQFJUCQIEVPVSYVF9TZT30, AIRBNB, /ROC/02264857600000154	557.22	1009	60	P
00003008	1	145179247	BORONA GLOGIC, ILIJE PLAMENCA, PODGORICA, 81000 ME	GB	375.20	EUR	22.04.2021	15.00	2334		1	502	GDSV/BORONA GLOGIC, /ROC/197934142	375.20	1009	60	P
00003040	1	145361079	WORLDPAY AP LTD, 25 WALBROOK, GB/LONDON/EC4N 8AF	GB	44.91	USD	23.04.2021	10.00	2334		1	502	0GG1AIOTMUOSUQK8HLHVNYRLTR0, AIRBNB, /ROC/02316620800000004	44.91	1009	60	P
00003040	1	145435347	WORLDPAY AP LTD, 25 WALBROOK, GB/LONDON-EC4N 8AF	GB	586.88	USD	26.04.2021	10.00	2334		1	502	0GG1AZI9W5VBCKXRYDJMWVTPBY1, AIRBNB, /ROC/02335329300000263	586.88	1009	60	P
00003036	1	145553835	DAVID MORAN, 23 RODITIS DR OCEAN GROVE VIC 3226, Australia	AU	1621.80	EUR	29.04.2021		2334		1	502	T +38269430905 Gift anniversary	1621.80	1009	60	P
00003040	1	145635412	1/DEUTSCHE POST AG-NL RENTEN SERVIC, 2/VENLOER STR 151-153, 2/KOELN, D	DE	3582.07	EUR	30.04.2021	15.00	2334		1	502	PENSION 04.21 PVA REFNO1379849, /ROC/1379849042021850	3582.07	1009	60	P
00003040	1	145644244	1/DEUTSCHE POST AG-NL RENTEN SERVIC, 2/VENLOER STR 151-153, 2/KOELN, D	DE	1750.04	EUR	30.04.2021	15.00	2334		1	502	PENSION 04.21 PVA REFNO1407274, /ROC/1407274042021850	1750.04	1009	60	P

DT2:

id	Dates	Currency	Rate
3970	4/1/2021	USD	1.172500000
3971	4/1/2021	GBP	0.852090000
3972	4/1/2021	CHF	1.107000000
3973	4/2/2021	USD	1.174600000
3974	4/2/2021	GBP	0.851950000
3975	4/2/2021	CHF	1.109900000
3976	4/3/2021	USD	1.174600000
3977	4/3/2021	GBP	0.851950000
3978	4/3/2021	CHF	1.109900000
3979	4/4/2021	USD	1.174600000
3980	4/4/2021	GBP	0.851950000
3981	4/4/2021	CHF	1.109900000
3982	4/5/2021	USD	1.174600000
3983	4/5/2021	GBP	0.851950000
3984	4/5/2021	CHF	1.109900000
3985	4/6/2021	USD	1.174600000
3986	4/6/2021	GBP	0.851950000
3987	4/6/2021	CHF	1.109900000
3988	4/7/2021	USD	1.181200000
3989	4/7/2021	GBP	0.853580000
3990	4/7/2021	CHF	1.107000000
3991	4/8/2021	USD	1.188400000
3992	4/8/2021	GBP	0.860650000
3993	4/8/2021	CHF	1.104400000
3994	4/9/2021	USD	1.187300000
3995	4/9/2021	GBP	0.862900000
3996	4/9/2021	CHF	1.102100000
3997	4/10/2021	USD	1.188800000
3998	4/10/2021	GBP	0.866580000
3999	4/10/2021	CHF	1.101000000
4000	4/11/2021	USD	1.188800000
4001	4/11/2021	GBP	0.866580000
4002	4/11/2021	CHF	1.101000000
4003	4/12/2021	USD	1.188800000
4004	4/12/2021	GBP	0.866580000
4005	4/12/2021	CHF	1.101000000
4006	4/13/2021	USD	1.190400000
4007	4/13/2021	GBP	0.865180000
4008	4/13/2021	CHF	1.099800000
4009	4/14/2021	USD	1.189600000

[ashwin.ashok ((TheCodingTheory))]

Hi @Olivera_Kalinic ,

Could you please share dt1 as an excel spreadsheet?

The solution is almost complete, just need to test it with actual data.
First you have to declare a Dictionary of String, String and convert dt2 into a suitable key-value pairs.

dt2.AsEnumerable().ToDictionary(Function(k) k("Dates").ToString+k("Currency").ToString,Function(v) v("Rate").ToString)

After that, we have to clone dt1 and add a DataColumn “Rate” which will hold the rates(since I didn’t find any rate column in the dt1 from above).

Then the last step would be to lookup the values:

(From row In dt1.AsEnumerable()
Let rate = dict_Rates(row("DATUM VALUTE").ToString+row("VALUTA").ToString)
Let ra = row.ItemArray.Append(rate).ToArray()
Select dt_final.Rows.Add(ra)).CopyToDataTable()

Sometimes excel will interpret dates as OA Dates, so that is why I wanted to test it with some actual data.

This is the sequence so far, and I will update it once you provide the excel files.
LookupTwoColumn.xaml (6.9 KB)

Kind Regards,
Ashwin A.K

[Olivera_Kalinic (Olivera Kalinić)]

Here are sample files:

dt1.xlsx (417.0 KB)
dt2.xlsx (177.4 KB)

Just to notice that the error “Assign: The given key was not present in the dictionary.” appears when it comes to last Assign activity in the process.

[ashwin.ashok ((TheCodingTheory))]

Hi @Olivera_Kalinic ,

A lot of them are tagged with EURO and its missing from dt2, so could you verify whether the sequence performs the operation as expected?

dt2.AsEnumerable().ToDictionary(Function(k) Convert.ToDateTime(k("Dates").ToString).ToString("ddMMyyyy")+k("Currency").ToString.Trim,Function(v) v("Rate").ToString.Trim)

(From row In dt1.AsEnumerable()
Let key = DateTime.ParseExact(row("DATUM VALUTE").ToString,"dd.MM.yyyy",Nothing).ToString("ddMMyyyy")+row("VALUTA").ToString.Trim
Let rate = If(dict_rates.Keys.Contains(key),dict_Rates(key),"")
Let ra = row.ItemArray.Append(rate).ToArray()
Select dt_final.Rows.Add(ra)).CopyToDataTable()

LookupTwoColumn_v1.xaml (8.8 KB)

Kind Regards,
Ashwin A.K

[Olivera_Kalinic (Olivera Kalinić)]

It works, thanks a lot :).