Nope. Only unawesome ones. Like for each record checking each field if it contains a value and keep count of the number of fields in the record that have a value. Then keep only the records with the largest counts per Ref value. And then keep only the first records per Ref with the largest counts since there could still be multiple records with the same count.
Thank you for your reply,
So the unawesome ones are the only solution...
after reviewing my database, i can simplify my problem :
Ref date 1 date 2 Reference 1 date Reference 1 date Reference 1 date Reference 1 date date Reference 1 date Reference 1 date
I'd like to keep the line in Yellow and remove other ones, but if there isn't data in the date 2 field keep the first record. All that for all my References... How would you do ?
for people who follow the stream,
i found a solution for the case above (It works for date type only):
What I did :
Concat(date1) as dateone,
Concat(date2 as datetwo
FROM [lib://test/Microsoft Excel Worksheet.xlsx]
(ooxml, embedded labels, table is Feuil1)
Group by [Reference];
left join DATA:
if(len(trim(dateone))>10,date(right(dateone,10)),date(dateone)) as date1,
if(len(trim(datetwo))>10,date(right(datetwo,10)),date(datetwo)) as date2
Drop field dateone, datetwo from DATA;
store DATA into [lib://test/test.csv] (txt, delimiter is ';');