Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
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;
Thank you very much!