Using Variable to only return data for beginning of week

[kaitlynwagner_s]

Hey All!

I have a QVD that takes a nightly snapshot of a bunch of data. I would like to start tracking changes over time, however I really only need the first day of the week's snapshot from the QVD. I tried to create a Variable in my Editor to do this for me, but i am getting no results loaded (No errors or anything, just no results). Here is my script:

WeekstartPeriod:

LOAD

DISTINCT
weekstart(DataLoadDate,0) as WeekStartDate
FROM [lib://Qlik_Resources/Outstanding_KPI.qvd]
(qvd);

Let WeekStartPeriod = WeekStartDate;

LOAD

AccountCode,
AccountName,
Address,
City,
State,
Country,
Telephone,
Email,
InvoicingEntity,
InvoiceCode,
InvoiceNumber,
usrname,
"Comment",
DataLoadDate
FROM [lib://Qlik_Resources/Outstanding_KPI.qvd] (qvd)
where Date(DataLoadDate) = $(WeekStartPeriod)
;

[Taoufiq_Zarra]

Let WeekStartPeriod = WeekStartDate;

to transfer values into variable we use

LET WeekStartPeriod = peek('WeekStartDate');

or

Let WeekStartPeriod = FieldValue('WeekStartDate',1);

but you need to check how many rows you'll get in LOAD DISTINCT weekstart(DataLoadDate,0) as WeekStartDate before you use it.

[Kushal_Chawda]

Probably you need this but let me know if my understanding is wrong

WeekstartPeriod:

LOAD
DISTINCT
date(weekstart(max(DataLoadDate),0)) as WeekStartDate
FROM [lib://Qlik_Resources/Outstanding_KPI.qvd]
(qvd);

Let WeekStartPeriod = peek('WeekStartDate',0,'WeekstartPeriod');

LOAD
AccountCode,
AccountName,
Address,
City,
State,
Country,
Telephone,
Email,
InvoicingEntity,
InvoiceCode,
InvoiceNumber,
usrname,
"Comment",
DataLoadDate
FROM [lib://Qlik_Resources/Outstanding_KPI.qvd] (qvd)
where Date(DataLoadDate) = '$(WeekStartPeriod)'
;