Qlik Sence - Generated dates connect with dates from database.

MarkSYPO — Thu, 05 Aug 2021 16:49:09 GMT

Hello guys,

I'm facing some problem with connect between.

first I have generated date range see below:

LET Start = num(makedate(2020,06,1)); LET End = num(today()); LET NumOfDays = End - Start + 1; Date_src: LOAD $(Start) + Rowno() -1 as DateID AUTOGENERATE $(NumOfDays); Calendar: LOAD DateID, // just in case date(DateID) as Date, // it will be in format defined in your SET DateFormat=, or in your system format day(DateID) as Day, week(DateID) as Week, month(DateID) as Month, // simple month name; it is dual - numeric and text year(DateID) as Year, weekday(DateID) as Weekday, dual(month(DateID) & '-' & year(DateID), year(DateID) & num(month(DateID), '00')) as MonthYear, // Month-Year format, dual 'Q' & ceil(month(DateID)/3) as Quarter, //Q1,Q2,Q3,Q4 dual('Q' & ceil(month(DateID)/3) & '-' & year(DateID), year(DateID) & ceil(month(DateID)/3)) as QtrYear // Qn-Year, dual // Q1-2019 RESIDENT Date_src; Drop Table Date_src;

now i want to use this dates for filtering some results which are come from database / REST API.

RestConnectorMasterTable: SQL SELECT "channel", "call_centre", "country", "name", "email", "question", "answer", "correct", "created", "video", "date", "time", "prod", "sps" FROM JSON (wrap on) "root"; [Quiz_analyst]: LOAD [channel] AS [quiz-analyst-channel], [call_centre] AS [quiz-analyst-call_centre], [country] AS [quiz-analyst-country], [name] AS [quiz-analyst-name], [email] AS [quiz-analyst-email], [question] AS [quiz-analyst-question], [answer] AS [quiz-analyst-answer], [correct] AS [quiz-analyst-correct], [created] AS [quiz-analyst-created], [video] AS [quiz-analyst-video], [date] AS [quiz-analyst-date], [time] AS [quiz-analyst-time], [prod] AS [quiz-analyst-prod], [sps] AS [quiz-analyst-sps] RESIDENT RestConnectorMasterTable; DROP TABLE RestConnectorMasterTable;

Is any chance for connect it ?

[created] AS [quiz-analyst-created]

Many Thanks

Re: Qlik Sence - Generated dates connect with dates from database.

Lisa_P — Fri, 06 Aug 2021 00:28:45 GMT

The associations are based on field names, so to make an association you can change the name to the same in both.

eg in Calendar table

DateID as [quiz-analyst-created],

...

Or you could load your data first and use that to derive your calendar.

topic Qlik Sence - Generated dates connect with dates from database. in App Development

Qlik Sence - Generated dates connect with dates from database.

Re: Qlik Sence - Generated dates connect with dates from database.