topic Re: Date sort nor working in script in QlikView

Date sort nor working in script

Anonymous — Fri, 03 Jun 2016 16:52:26 GMT

Hi folks, I'm working in a new dashboard in which I need to calculate 3 month rolling averages in a table, so I'm using aggr, and to make it work, the data should come pre-sorted by date from the script load, so... I'm loading my data into a temp table, then just doing a resident load order by date field. but it is not working, see the attached image, that's the result of sorting by "load order -> original". I've checked and the field is right, date format... Any suggestions? Thanks in advance!

Re: Date sort nor working in script

sunny_talwar — Fri, 03 Jun 2016 16:53:46 GMT

It has to be sorted the first time it is named this. Can you share your script?

Re: Date sort nor working in script

Anonymous — Fri, 03 Jun 2016 16:56:11 GMT

Script attached

Re: Date sort nor working in script

sunny_talwar — Fri, 03 Jun 2016 16:58:32 GMT

Try this out:

TEMP_POI:

LOAD ...

DATE(DATE#(DELIV_MONTH & '-' & DELIV_YEAR,'MMMM-YYYY'),'MMM-YY') AS MONTH1,

...

FROM

(qvd)

WHERE upper(CUSTOMER_CHANNEL_GROUP & CUST_RTM_GROUP) <> 'CONSUMERDIRECT';

Concatenate

LOAD...

DATE(IF((ISNULL(BILL_DOC_DT) OR BILL_DOC_DT = '') AND SRC_SYS_KY=27,MONTHSTART(SO_CLOSE_DT),MONTHSTART(BILL_DOC_DT)),'MMM-YY') AS MONTH1,

...

FROM

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

WHERE upper(CUSTOMER_CHANNEL_GROUP & CUST_RTM_GROUP) <> 'CONSUMERDIRECT';

POI:

LOAD *, 1 as flag,

MONTH1 as MONTH

RESIDENT TEMP_POI

ORDER BY DATE_SORT;

DROP TABLE TEMP_POI;

Re: Date sort nor working in script

Anonymous — Fri, 03 Jun 2016 16:59:08 GMT

BTW I tried sorting by field "MONTH" and "DATE_SORT" with same results

Re: Date sort nor working in script

Anonymous — Fri, 03 Jun 2016 17:04:28 GMT

It worked! so how come just re-naming the field makes it work?

Re: Date sort nor working in script

sunny_talwar — Fri, 03 Jun 2016 17:06:56 GMT

Its not just the renaming. The load order works at the first encounter of when the field is created. In your example, MONTH was not sorted when it was created, which is why the load order was all messed up. Whereas I renamed it to MONTH1 for time being and then renamed it again to MONTH when we used the SORT to make sure MONTH was created in the sort order we wanted.

I hope this make sense