Страницы: 1
RSS
Поиск и подстановка значения даты по нескольким параметрам при условии неточного совпадения искомой даты с датой в диапазоне поиска, За сложным названием кроется еще более сложная задача
 
Доброго времени суток уважаемые Форумчане!!!

Много лет читаю форум, являюсь пользователем PLEX и вот, наконец то, столкнулся с задачей, которую не могу решить самостоятельно. На работе столкнулся с нетипичной задачей. Для упрощения опишу ее понятными всем терминами, чтобы не вдаваться в специфику работы (см. примечание):

Вводные данные:
  1. Есть агенты за которыми закреплены магазины в определенных городах и странах (вкладка "Агенты")
  2. Зоны ответственности агентов не пересекаются (т.е. каждый агент отвечает за свои магазины в своей стране)
  3. Агенты периодически посещают свой магазин (вкладка "визиты агентов")
  4. Данные по визитам записываются, но в таблице виден лишь первый день визита агента и продолжительность визита
  5. Визит может быть 1 или 2-х дневный
  6. Во время любого из дней визита агент должен подтвердить (верифицировать) данные по магазину в базе данных
  7. В базе автоматически фиксируется дата действия агента, имя агента, название магазина по которому верифицируется данные ("вкладка результаты визитов")
  8. Некоторые агенты не успевают сделать верификацию во время визита в магазин и делают ее позже (например от 3 до 25 дней)
  9. У меня есть три таблицы с этими данными
  10. Нужно в таблице "результаты визитов" установить дату визита агента в данный магазин
Файл с примером во вложении. Я его создавал по образу реальной задачи, но в настоящей списке около 100 000 строк с документальным следом. Там около 30% агентов делали верификацию не в срок и мне нужно понять какая была дата визита в каждом конкретном магазине. Учитывая, что я создавал файл сам и задавал интервалы для ошибок, то в файле есть лист с ответами для того, чтобы вам можно было ориентироваться на предмет правильности ответа. Это при условии если кто-то дочитал до этого места и не потерял желание посмотреть и решить задачу :D.

Мои действия по решению задачи.
Даты визитов с верификацией в день однодневного визита срок вычислялись легко и быстро. Я вставил дополнительный столбец "помощник" и функцию СЦЕПИТЬ для имени агента и дату верификации. А также сделал это в таблице визитов. Функция ВПР сделала работу для точных совпадений. Остался список где есть верфикация не в первый день визита или не в срок и вот тут я "въехал в пень". Не могу сообразить как реализовать поиск по сложному условию (дата по которой ведется поиск плавающая). Частично решил задачу для 2 дневных визитов, но с ошибками и решил идти к вам за помощью.

Примечание: Если сильно любопытно, то я могу в e-mail рассказать реальные вводные задачи тому кто мне поможет с таблицей. Магазинами в реальности и не пахнет да и коммерческого интереса в этой задаче нет. Просто это одна из нетипичных задач с которыми я сталкиваюсь довольно часто. Задача возникла впервые и не факт, что будет повторяться. Именно поэтому выкладываю ее сюда, а не в предложение работы.

Спасибо вам за терпение (даже если вы просто прочитали этот текст) ну и отдельное спасибо за любую помощь. Мне, честно говоря, достаточно будет общего направления для понимания решения...Хотелось бы обойтись без макросов (если это конечно возможно)

С уважением,
Олег
 
Цитата
Oleg Kozlov написал: в настоящей списке около 100 000 строк с документальным следом.
тогда лучше работать как с БД - запросами, - или писать макрос... или см в сторону PowerPivot
Цитата
Oleg Kozlov написал:  то в файле есть лист с ответами для того, чтобы вам можно было ориентироваться на предмет правильности ответа.
лучше изобразите что есть и что надо (реал образец всех данных, как и вся поднаготная, работы агента,  в принципе, не нужны, лишь отображение проблемы) - сориентируйтесь в правильности ответов сами  ;) - и очертите конкретику вопроса - что из какого столбца куда по какому критерию...
Цитата
Oleg Kozlov написал: Не могу сообразить как реализовать поиск по сложному условию (дата по которой ведется поиск плавающая).
что плавает, в каких пределах (полагаю с запаздыванием - при сдаче инфо позже установленного срока) - значит, наверно, стоит поставить условие на >=(начала визита например), какое значение найти (столбцов у вас много) , куда отобразить (как должно выглядеть на выходе)... просто текста много, вопрос расплывчат - какое условие для формулы (или логики)?? - я бы сказала: краткость - сестра таланта  :)
p.s. хотя для 100 000 строк - формулы  - наверно, не выход!...
Изменено: JeyCi - 25.07.2015 18:19:20
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
А с какой периодичностью агенты посещают мага
Цитата
Oleg Kozlov написал:
В базе автоматически фиксируется
Скажите, а в этой базе случайно нет поле-ключа в таблице визитов?
и Его же в таблице отчетов?
Изменено: TheBestOfTheBest - 25.07.2015 18:58:22
Неизлечимых болезней нет, есть неизлечимые люди.
 
JeyCi, pivot на десктопе (без сервера физического) тоже не тянет миллионы кортежей :( Access самое оно, причём лучше, как ни странно 2003.
Oleg Kozlov, я понимаю задачу так:
-есть на каждую дату визита несколько дат отчёта (т.н."верификации где-то"): смотрим, например, Сидоров Пётр, магаз 14, январь 15г. - 1 "визит" и 3 "отчёта", которые относятся к нему, судя по листу ответов;
-нам нужна формула (или запрос в БД), которая на каждую дату отчёта ищет дату визита (для этого лица, в этот магаз) которая раньше или в тот  день(<=). Если их несколько - берёт последнюю.
так?
Тогда это типичная задача СУБД, но если это критично можно решить и в xls.
ЗЫ
СУБД можно сделать и промежуточным звеном: она получает данные из 1 листа, обрабатывает и выдаёт по запросу во 2 лист. действует без открывания самой СУБД
.
Изменено: A.nod - 25.07.2015 18:55:09
Программист - человек, который решает задачу, о которой никто не знает, таким способом, который никто не понимает.
 
Спасибо всем за участие и потраченное время!!!

Предполагал, что есть простое решение, которое для меня не очевидно и поэтому выложил вопрос на форум. Но раз навскидку решения не нашлось, то видимо действительно надо искать другой подход...Может быть дождусь UDF, которую Николай планирует разработать для поиска по нескольким критериям  ;).

@JeyCi - Если бы я мог, то выложил бы исходные файлы, но я связан соглашением о неразглашении. Однако, созданный мною образец максимально приближен к боевой задаче...ближе уже некуда  :) и в нем максимально точно обрисована проблема - поиск совпадения при условии неточного совпадения по дате с различным разбросом. Power Pivot мне мало знаком и поэтому я его не рассматривал. Написание макроса тоже не вариант так как нужно просить кого то написать, а смысла в этом нет так как задача одноразовая и тратить на нее время другого человека считаю нецелесообразным. Кратко описать задачу не смог так как есть детали без указания которых невозможно понять ее специфики.

@TheBestOfTheBest - В отношении поля ключа: такого в базе, к сожалению, нет иначе бы я не спрашивал. Есть три идентификатора - имя агента, магазин и дата верификации.

Промежуточным решением вижу округление даты верификации до месяца и года равно как и округление даты визита до месяца, чтобы уменьшить количество неизвестных значений (верификация в следующем месяце).

Итого: Подожду еще неделю и закрою тему  8)

Еще раз всем спасибо за участие и комментарии, а также за время! Всем хороших выходных!!!
 
А как вы понимаете, что такой-то отчет составлен к именно такому посещению? Если именно в этом задача, то боюсь вас разочаровать... только вручную, или с помощью алгоритма (типа дата+- 20 дней). Но алгоритм не даст 100% результата, придется допиливать напильником ручками! Я бы в таблицу с отчетами добавил ключ Агент+Магазин+ДатаПосещения и вышеуказанным способом проставил бы этот ключ.

Получается ваши агенты отчитываются не понимая сами за какое посещение? Ведь через 25 дней можно и не вспомнить куда и зачем ходил, что делал.
Сидоров Петр отчитался 2-го числа, а в магазин съездил 3-го, это правильно?
Изменено: TheBestOfTheBest - 25.07.2015 19:25:15
Неизлечимых болезней нет, есть неизлечимые люди.
 
Все немного сложнее ))) Данные с отчетами и датами визитов идут из одной базы данных. Данные по верификации из другой базы (не связанной с первой базой). Верификация - одна из многих активностей (притом не самая главная). Моя задача максимально точно свести эти данные вместе (по возможности), чтобы понять дату визита.
И да, агенты очень хорошо понимают за что отчитываются :)

Пример неверен: Петров съездил 2-го и 3-го в магазин #1 и отчитался 2-го, а в магазине #2 был 15-го, но отчитался 18-го, потом был в магазине #3 27-го и отчитался 29-го. Как то так.

Еще раз благодарю за потраченное время и наводки.
Изменено: Oleg Kozlov - 25.07.2015 19:29:58
 
Я сразу предупреждал,  что задача достаточно нетривиальная :)
 
@A.nod - мне нужно даты отчетов привязать к дате визита. При словах СУБД взгляд мой погрустнел и замутился :) так я в этом ничего не понимаю. Но похоже пора начинать.  
Изменено: Oleg Kozlov - 25.07.2015 19:38:28
 
Вот что мешало в таблицу отчета включить графу Дата посещения? Никакой нетривиальности бы не было. И сделать её обязательным для заполнения полем.
Изменено: TheBestOfTheBest - 25.07.2015 19:39:26
Неизлечимых болезней нет, есть неизлечимые люди.
 
Цитата
Oleg Kozlov написал: Кратко описать задачу не смог так как есть детали без указания которых невозможно понять ее специфики.
для автоматизации - хоть НЕкратко, но описать надо - откуда что брать при каких условиях и куда ложить (как куда и зачем ходят агенты - программа даже не спросит - это учесть лучше вам) - и всё... если данная инфо и нужная инфо не поддаются логическим связям друг с другом, то и автоматизировать не получится... я не настаивала (предложила как вариант - переформулировать - чтобы быстрее дождаться помощи)... успехов
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
@TheBestOfTheBest - Это уже вопрос риторический. Более того мое сказать что этого никто делать не будет (есть своя специфика).
 
 @JeyCi - может так. Есть база с датами визитов агентов в определенные магазины и есть другая база - с датами действий агентов по данному магазину в другой базе. Мне нужно сравнить дату действия агента в базе со списком визитов агента и выявить совпадение при условии, что агент иногда документирует действия с задержкой. Конечная задача привязка даты действий агента к дате визита агента в этот магазин. Не знаю как проще :(
Изменено: Oleg Kozlov - 25.07.2015 19:49:52
 
Если представленные данные реальные, то агенты отчитываются чаще, чем посещают магазины.  
Неизлечимых болезней нет, есть неизлечимые люди.
 
@TheBestOfTheBest - Это как раз нормально так как агент может после визита заходить в базу несколько раз и каждый  раз документируется
Изменено: Oleg Kozlov - 25.07.2015 20:32:30
 
наверно, ещё так можно проверить несвоевременность верификации в базе...
Код
SELECT DISTINCT vv.агент, vv.страна, vv.город, vv.магазин, FORMAT(vv.`дата визита`, 'dd.mm.yyyy') as `дата визита`, vv.`длительность визита`,  
rr.`действие в базе`, rr.`дата действия` AS `дата действия`
FROM `'визиты агентов$'` AS vv,  `'результаты визитов$'` AS rr
WHERE vv.агент = rr.Агент  AND vv.магазин = rr.магазин  AND (vv.`дата визита`+ vv.`длительность визита`)< rr.`дата действия`
ORDER BY vv.агент, FORMAT(vv.`дата визита`, 'dd.mm.yyyy'), rr.`дата действия`
вывод в табл зашедших позже срока (дата визита)+(длительность визита) - столбец H с превышением срока... НО при справедливости замечания #15 - несовсем валидный результат, т к надо ещё убрать тех, кто до этого всё-таки успел отметиться вовремя... возможно, как-то дополнить инструкцию SQL - операторами EXCEPT или MINUS... но  
Цитата
The MINUS operator is not supported in all SQL databases. It can used in databases such as Oracle.
For databases such as SQL Server, PostgreSQL, and SQLite, use the EXCEPT operator to perform this type of query.
но если всё-таки (как вы сказали)
Цитата
Oleg Kozlov написал: Конечная задача привязка даты действий агента к дате визита агента в этот магазин
то условие неравенства можно заменить равенством - чтобы привязать добросовестных сотрудников к сдаче в срок...
Код
(vv.`дата визита`+ vv.`длительность визита`)= rr.`дата действия`
... или  сверять на попадание в период от начала визита до (его дата+длительность)... вариантов много- иначе как и чего они входят в базу, не отмечая причины в базе, гадать можно долго...... не совсем быстро придумывать работающие инструкции sql. как и писать макрос... жаль, что много времени ушло на лирику... (поэтому не имею возможности более времени посвятить теме)... всё потому что изначально не продумано желаемое

p.s. в файле вложенном - (табл, не участвующие в запросе, удалены - чтобы не превышать лимит размера файла, установленный правилами форума)
p.p.s чтобы поиграться с запросом - следуя традиции -  файл должен находиться в папке C:\1\ , на таблице правой кнопкой - Обновить
Изменено: JeyCi - 26.07.2015 10:31:42
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Всем ОГРОМНОЕ спасибо! ПРОШУ ЗАКРЫТЬ ТЕМУ :) !
Страницы: 1
Наверх