topic Re. :Is there something wrong with this? or another way to do it? in QlikView

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

patrickanderson — Tue, 22 Feb 2011 17:26:04 GMT

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;

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

vgutkovsky — Tue, 22 Feb 2011 17:48:05 GMT

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,

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

martin59 — Tue, 22 Feb 2011 17:49:36 GMT

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 ?