Is there something wrong with this? or another way to do it?

[patrickanderson]

The goal is to take the dates and the SHPSLSLBS and roll them up into weeks,

Everything works but the week code still comes out D/M/YYYY which is not what i want. I want YYYYMMDD format to i can tie to other data.

Is there something wrong with my method here? you can change date formats in the load script right?

LDESource:

LOAD DTNUM,

weekend(ACTSHIPDATE,0,-1) AS Week,

SAP_CUSTNO

FROM

(qvd)

WHERE ORDDATE >= '1/1/2010';

Left Join

LOAD DTNUM,

PARTNO,

ORDSLSLBS,

ORDSHPLBS,

ADJSLSLBS,

ADJSHPLBS,

SHPSLSLBS,

SHPSHPLBS

FROM

(qvd)

where exists (DTNUM);

LDE:

Load

date(Week,'YYYYMMDD') as Week,

SAP_CUSTNO,

PARTNO,

sum(SHPSLSLBS) as LDE_Volume

RESIDENT LDESource

GROUP BY Week, SAP_CUSTNO,PARTNO ;

Drop Table LDESource;

[vgutkovsky · Accepted solution]

Well "date(Week,'YYYYMMDD') as Week" just tells QV that "Week" is already in the format "YYYYMMDD" and to use that format to read the date properly. Meaning, it's not used for writing the date. So you can either change the variables at the beginning of the script (SET DateFormat=YYYYMMDD';) or make that particular date function a date transform: date#(date(Week,'YYYYMMDD'),'YYYYMMDD') as Week.

Regards,

[martin59]

Hi,

You have to use the formatting function "date()" like this :

LOAD ... , Date(Weekend(ACTSHIPDATE,0,-1),'YYYYMMDD') as Week... FROM ...

Is that you want ?