Hello everyone.
I am working on a script that imports data from a SQL database developed by someone else. The date filter panes work just fine, except for the data provenient from tables that I imported from excel spreadsheets. I would like the date filter panes to work for all the data imported, wherever it comes from.
As I can see in the data model viewer, there is a linktable which has a field named Date that links to a table named Calendar, which contains fields as day, month, year, quarter, etc. The data I want to apply the filter to are those contained in tha tables CUSTOENTREGAS and CUSTOPROMOTORES (imported from excel spreadsheet). These two tables have date fields and are already linked to another table through CustomerID fields. It seems that I have to link them to the linktable (or other table) through their date fields.
Any help would be very appreciated!
This is the script for the link table:
LinkTable:
LOAD DISTINCT
AutoNumberHash128(cdFilial,cdRepres,cdCliente,CODP,dtLan,cdCir) as %LinkTableGeral
,cdFilial as [IDFilial]
,cdCliente as [IDCadastroGeral]
,cdRepres as [IDRepresentante]
,CODP as [IDProduto]
,dtLan as Data
,cdCir as [IDCirurgia]
,AutoNumberHash128(cdFilial,cdCliente,cdPed) as %IDPedido;
SQL
SELECT
M.[cdFilial]
,M.[cdCliente]
,CASE WHEN D.[cdRepres] IS NULL THEN 0
ELSE D.[cdRepres]
END [cdRepres]
,M.[CODP]
,CASE WHEN D.[cdCir] IS NULL THEN 0
ELSE D.[cdCir]
END [cdCir]
,CONVERT(Date,M.dtLan,103)as dtLan
,P.cdPed
FROM Mov M
LEFT JOIN DF D
ON (M.[cdFilial] = D.[cdFilial]
AND M.[cdCliente] = D.[cdCliente]
AND M.[cdDF] = D.[cdDF])
LEFT JOIN MovPI P ON(M.cdi = P.cdi)
Where m.nItem is not null
ORDER BY M.dtLan ASC;
TMP_DataComercial:
SQL
SELECT
CONVERT(Date,Pe.dtEmissao,103) as [Data]
FROM PedItem PeI
LEFT JOIN Ped Pe
ON (PeI.cdPed = Pe.cdPed);
TMP_DataComercial:
SQL
SELECT
CONVERT(Date,M.dtLan,103) as [Data]
FROM Mov M
LEFT JOIN DF D
ON (M.[cdFilial] = D.[cdFilial]
AND M.[cdCliente] = D.[cdCliente]
AND M.[cdDF] = D.[cdDF])
LEFT JOIN MovPI P ON(M.cdi = P.cdi)
Where m.nItem is not null
ORDER BY M.dtLan ASC;
CALLENDARIO:
NoConcatenate
LOAD
num([Data]) as Data,
Year([Data]) as Ano,
Month([Data]) as Mês ,
Day(num([Data])) as Dia,
num(month(Data),00) as NR.Mês,
DayNumberOfQuarter(Data) as [Dia Trimestre],
DayNumberOfYear(Data) as [Dia Ano],
MonthName(Data) as [Mes Ano],
QuarterName(Data) as [Quarter],
Week(Data) as [Semana],
WeekDay(Data) as [Dia Semana]
Resident TMP_DataComercial
Order by [Data] asc;
DROP table TMP_DataComercial;
