Load date between 2 variables

[twanqlik]

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 · Accepted solution]

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]

Thank you very much!