Джойним таблицы — как избавиться от дублей строк

Джойны в Tableau — это прекрасный полезный способ добавления новых измерений и мер в наш анализ. Но без понимания того, как они влияют на набор данных, мы можем получить завышенные значения из-за дублирования строк. Один из примеров — пользователь пытается решить проблему конвертации валюты (продажи происходят в разных странах). К своему основному источнику данных он должен присоединить еще один набор данных, содержащий ежемесячные обменные курсы (с данными по месяцу и стране).

Подумаем, как объединить несколько источников и несколько вычислений, чтобы гарантированно получить правильные ответы — даже при объединении по нескольким измерениям (страна и месяц).

Объединение (джойн) разных источников данных в Tableau

Будем использовать источник данных Sample — Superstore. Мы можем добавить дополнительные поля в наш анализ — при условии, что основная таблица и таблица, содержащая новые поля, имеют по крайней мере одно общее измерение.

Например, добавим столбец из таблицы Returns в таблицу Orders (из бесплатного набора данных Sample – Superstore). Соединение будет выглядеть так:

Поскольку обе таблицы содержат измерение Order ID, левое присоединение таблицы Returns к таблице Orders добавит новый столбец «Returned» в набор данных. Для каждого Order ID (идентификатора заказа) будет YES или NULL — чтобы знать, какие заказы были возвращены. Можно использовать этот новый столбец в качестве flag (флага) и/или создать вычисляемое поле для подсчета количества возвратов.

Проблема в том, что таблица Returns сообщает только о том, был ли возвращен заказ на уровне Order ID, а таблица Orders может иметь несколько строк на идентификатор заказа (в случае, если в одном заказе были приобретены несколько продуктов). В этом случае соединение вызывает дублирование строк. Например, идентификатор заказа CA-2015-143336 содержит три продукта, и в результате джойна для каждого продукта создаются 3 поля, 2 из которых новые.

В итоге получаем странные значения — продажи продукта Newell 341 в этом заказе увеличились с $9 до $27.

Как убрать дублирование с помощью LOD (Level of Detail)

Самый простой способ исправить этот тип дублирования строк — создать вычисляемое поле с выражением FIXED LOD и выделить одно значение для наиболее низкого уровня детализации в соединении.

Во-первых, для иллюстрации проблемы просмотрим количество записей. Поместим сгенерированное поле Number of Records на карточку  Text Marks. В исходной таблице Orders (без джойна) это действие выведет 9 994 записи.

Если это же действие применить к сджойненному набору данных, получим 12 420 записей:

Это означает, что у нас 2 426 дублированных строк. И это создает проблемы — неверные данные и неверные выводы.

Например, нужно посчитать количество возвращенных заказов. Для этого создаем вычисляемое поле для подсчета числа «1» каждый раз, когда измерение Returned равно “Yes”.

Помещаем вычисление на карточку Text Marks и в итоге видим завышенные 3 226.

Вкладка Returns в файле Excel Sample — Superstore содержит только 800 строк, а не 3 226. Если нужно подсчитать только один возврат на заказ, нужно дедуплицировать значения Yes на уровне Order ID (идентификатора заказа). Если использовать агрегацию MIN, получим одно значение на Order ID:

LOD в Tableau уникальны тем, что можно использовать «агрегацию агрегации».  Мы уже сагрегировали количество возвратов на Order ID с помощью агрегации MIN в вычисляемом поле. Теперь просуммируем количество возвратов с помощью SUM:

Это означает, что по крайней мере один товар был возвращен в 296 различных заказах.

Дедупликация с помощью поля Row ID

Описанный выше метод полезен, когда требуется использовать меры из вторичного источника данных. Но можно сделать что-то подобное для использования и в первичном источнике данных.

Предположим, нам нужно поработать с мерой Sales из первичного источника данных. Сумма продаж до джойна составляет $2 297 201.

К сожалению, после джойна таблицы Returns и Orders мы получаем завышенное число $2 901 677:

Проще всего дедуплицировать строки в первичном источнике данных — при наличии измерения с уникальными Row ID для каждой строки в источнике данных. Малоизвестный факт, но набор данных Sample — Superstore имеет такое измерение. Получаем к нему доступ: клик на стрелку вниз в правом верхнем углу области Dimensions (измерений) на  панели Data и выбираем Show Hidden Fields (“Показать скрытые поля”).

Измерение под названием «Row ID» будет отображаться серыми буквами. Клик правой кнопкой мыши на измерении Row ID, выбираем Unhide и можно использовать измерение в вычисляемом поле.

Формула для дедуплицированных продаж (Deduplicated Sales):

{FIXED [Row ID]: MIN([Sales])}

Новый результат для Deduplicated Sales — ожидаемая сумма $2 297 201:

Как дедуплицировать с помощью LOD с более чем одним измерением

В начале поста мы говорили про компанию, которая хотела реализовать конвертацию валюты. У компании был обменный курс к долларам США для двух измерений: месяц и валюта. Чтобы смоделировать сценарий, настраиваем файл Excel, содержащий один год обменных курсов для канадских долларов и британских фунтов.

Поскольку набор данных Sample-Superstore содержит только доллары США, то «локализуем» американский доллар — показываем значение USD в местной валюте.

Делаем left join этой таблицы обменного курса с набором данных Sample-Superstore. Поля с типом данных Date имеют много нюансов, поэтому для удобства добавляем столбец в набор данных Sample-Superstore, который усекает каждую дату до месяца.

Если быстро собрать первичную таблицу и посмотреть на обменные курсы 2018 года, то увидим, что джойн опять вызвал завышенные значения:

Этот результат для анализа использовать нельзя. Он может привести к неверным выводам и потерям для бизнеса.

Чтобы исправить повторяющиеся строки и завышенные значения, вызванные соединением, будем использовать аналогичное выражение LOD, которое применяли в таблице Returns для дедупликации. Разница в том, что сейчас у нас несколько измерений для дедупликации. Поэтому разделяем все измерения запятыми сразу после FIXED LOD.

Формула будет выглядеть так:

Добавляем это вычисление в таблицу и все меняется:

Теперь, когда результаты обменного курса точные, можно использовать это дедуплицированное вычисляемое поле в другом вычисляемом поле — чтобы умножить наши исходные ежемесячные значения продаж на соответствующий ежемесячный обменный курс в каждой стране. Расчет будет примерно такой:

Вот график продаж — в USD и сконвертированный в канадские доллары или британские фунты (по верному курсу!):

 

 

Мы, компания АНАЛИТИКА ПЛЮС, с 2012 года помогаем нашим клиентам работать с данными — находить полезные инсайты и использовать эту информацию для увеличения прибыли компании.

За это время мы разработали и внедрили решения для компаний из разных сфер бизнеса: ритейл, дистрибуция, логистика, e-commerce, банки и финансы, маркетинг, телеком, производство, логистика, транспорт.

Мы помогаем начать работу с инструментами BI, предоставляем услуги «под ключ» — от сбора данных до установки ПО и предоставления финального интерактивного отчета. И конечно — проводим обучение: наши клиенты ни от кого не зависят и умеют работать со своими данными самостоятельно!

Как мы помогаем в работе с данными? >>

Хотите узнать, как провести анализ и сделать отчеты быстро?

 

Нам доверяют: