topic Re: so simple calendar, but how to do date syntax in load in App Development

so simple calendar, but how to do date syntax in load

daveatkins — Thu, 24 Aug 2023 22:09:06 GMT

What is wrong here? I have spent hours of trial and error and no closer to what must be a very simple solution that involves correct use of date format and $ expansion.

FOR i = 1 to 60
let vDate = Date(today() - i);
let v4w = if(i<=28,'Y','');

TRACE $(i) - last4w - $(=if(i<=28,'Y'));
TRACE test expression is $(v4w);

Calendar:
LOAD * Inline [
CensusDate, Last2m?, Last4w?, Last7d?, Last1d?
$(vDate), Y, $(=if(i<=28,'Y')),$(=if(i<=7,'Y')),$(=if(i=1,'Y))
];

NEXT i;

Re: so simple calendar, but how to do date syntax in load

daveatkins — Thu, 24 Aug 2023 22:48:09 GMT

sometimes posting the question leads you to the answer: double-interpolation. This accomplishes the desired task:

FOR i = 1 to 60
let vDate = Date(today() - i);
let v4w = if($(i)<=28,'Y','');
let v7d = if($(i)<=7,'Y','');
let v1d = if($(i)=1,'Y','');

Calendar:
LOAD * Inline [
CensusDate, Last2m?, Last4w?, Last7d?, Last1d?
$(vDate), Y, $(v4w),$(v7d),$(v1d)
];

NEXT i;

Re: so simple calendar, but how to do date syntax in load

marcus_sommer — Fri, 25 Aug 2023 13:59:29 GMT

I would use another approach with something like this:

load *, applymap('MyMap', RecNo) as Flag;
load date(today() - recno()) as Date, recno() as RecNo autogenerate 60:

Instead of an appropriate mapping-table which could be easily created in seconds In Excel you may remain by multiple and/or nested if-loops.

Further I would tend to apply everything within a single flag-field because it would be much easier to handle within the UI. At first it looked impossible because of the each other overlapping periods but it's not too hard by using (1) The As-Of Table - Qlik Community - 1466130.