topic Re: SQL to set analysis: Date field between two other date fields in App Development

SQL to set analysis: Date field between two other date fields

t_donnet — Sat, 16 Nov 2024 00:48:41 GMT

Hi,

I've this SQL query that i'ld to translate in set analysis:

select c.name, o.oderNum, s.NB, s.TotalSales
from Sales s, Client c, Order o
where s.condition = 20
and s.orderId = o.orderId
and o.clientId = c.clientId
and convert(date,CONVERT(varchar, s.SalesDate),112) between o.ValidFrom and o.ValidTo ;
and convert(date,CONVERT(varchar, s.SalesDate),112) between c.ValidFrom and c.ValidTo ;

this query is the base for the app and this is an simple example. The case is more complicated.
Fields date are in format 'DD.MM.YYYY'

How to translate in set analysis the dates of 'SalesDate' must be between the dates of 'ValidFrom' and 'ValidTo' of each tables?

Thanks for your help.

Regards,
Théo

Re: SQL to set analysis: Date field between two other date fields

Anil_Babu_Samineni — Thu, 21 Jan 2021 19:14:05 GMT

Set analysis can be done on aggregate basis at least per one measure column. As i can see, You can use modifier for above one like {<SalesDate={"=SalesDate>=ValidFrom and SalesDate<=ValidTo"}>}

Re: SQL to set analysis: Date field between two other date fields

t_donnet — Wed, 27 Jan 2021 16:16:38 GMT

Hi,

I tried different ways with your answer. The result isn't there.

How bank company use this kind SQL request in Qlik Sense? Do you think Qlik has a limit?

Regards,