2 Replies Latest reply: Aug 22, 2018 10:37 AM by Gysbert Wassenaar RSS

    Date filter pane not working for all tables

    Rhenan Freire Ferreira Folco

      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;