Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Date filter pane not working for all tables

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;

2 Replies
william_fu
Creator II
Creator II

Seems like this is what you need to implement:

Canonical Date

Gysbert_Wassenaar

Make sure the date values you load from excel actually are dates and not text strings that merely look like dates. Or if they are text strings use the date# function to convert them to dates.


talk is cheap, supply exceeds demand