Setting custom date variable from resident table

[#1]

Hi All,

I am trying to load date variables for 'yesterday' i.e. ThisWeek etc. from a resident calendar table but it doesn't seem to work..

Does nyone have any ideas pelase ?

Thanks in advance !! 🙂

Bob

OP_DATE:

LOAD Date ,

Week,

Year,

Period


FROM

C:\Analyser\Data\Cal.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);



Variables:

Load Week AS ThisWeek Resident OP_DATE where Date  = today(2)-1 ;

Load Period AS ThisPeriod Resident OP_DATE where Date  = today(2) -1 ;

Load Year AS ThisYear Resident OP_DATE where Date  = today(2) -1; 

[kaushiknsolanki · Accepted solution]

Hi,

     Try this.

OP_DATE:

LOAD Date ,

Week,

Year,

Period

FROM

C:\Analyser\Data\Cal.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Variables:

Temp:
Load distinct Week AS ThisWeek Resident OP_DATE where Date  = today(2)-1 ;

Let vThisWeek = peek('ThisWeek',0,'Temp');

Drop table Temp;

Temp:
Load distinct Period AS ThisPeriod Resident OP_DATE where Date  = today(2)-1 ;

Let vThisPeriod = peek('ThisPeriod',0,'Temp');

Drop table Temp;

Temp:
Load distinct Year AS ThisYear Resident OP_DATE where Date  = today(2)-1 ;

Let vThisYear = peek('ThisYear',0,'Temp');

Drop table Temp;

Regards,

Kaushik Solanki

[#3]

Thanks Kaushik

Works perfectly !!!!