topic Re: Load date between 2 variables in QlikView

Load date between 2 variables

twanqlik — Thu, 06 Jul 2017 22:06:48 GMT

I've created 2 variables based on a date field and I want to use those data variables in a where clause.

However, after running the scrip Qlikview messages that it can't find the used Variable fields.

What am i missing here?

See the attached images for a better overview of my issue.

SNAPSHOT_LAST_DAY_OF_MONTH:

LOAD

distinct SNAPSHOT_LAST_DAY_OF_MONTH

FROM

$(QVDpath)F_CONSUMER_CORE$(CurrentSnap).qvd

(qvd);

LET SNAPSHOT_MONTH=date(SNAPSHOT_LAST_DAY_OF_MONTH,'DD-MM-YYYY');

LET SNAPSHOT_MONTH6=date(AddMonths(SNAPSHOT_LAST_DAY_OF_MONTH,-6),'DD-MM-YYYY');

Test_Table:

LOAD

ACTIVITY_ID,

ACTIVITY_TYPE,

ACTIVITY_DATE,

date(ACTIVITY_DATE,'DD-MM-YYYY') as ACTIVITY_DATE_MONTH,

1 as test,

CONSUMER_ID_FK

Resident tmpEloqua

Where ACTIVITY_DATE_MONTH <= $(SNAPSHOT_MONTH) and ACTIVITY_DATE_MONTH >$(SNAPSHOT_MONTH6);

drop table tmpEloqua;

Exit Script;

sunny_talwar — Fri, 07 Jul 2017 00:55:24 GMT

May be try this

SNAPSHOT_LAST_DAY_OF_MONTH:

LOAD Max(SNAPSHOT_LAST_DAY_OF_MONTH) as MaxDate

FROM $(QVDpath)F_CONSUMER_CORE$(CurrentSnap).qvd (qvd);

LET SNAPSHOT_MONTH = Peek('MaxDate');

LET SNAPSHOT_MONTH6 = Num(AddMonths(Peek('MaxDate'), -6));

Test_Table:

LOAD ACTIVITY_ID,

ACTIVITY_TYPE,

ACTIVITY_DATE,

Date(ACTIVITY_DATE, 'DD-MM-YYYY') as ACTIVITY_DATE_MONTH,

1 as test,

CONSUMER_ID_FK

Resident tmpEloqua

Where ACTIVITY_DATE_MONTH <= $(SNAPSHOT_MONTH) and ACTIVITY_DATE_MONTH >$(SNAPSHOT_MONTH6);

DROP Table tmpEloqua;

Exit Script;

twanqlik — Sun, 09 Jul 2017 20:53:34 GMT

Thank you very much!