Date sort nor working in script

[Anonymous]

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!

[sunny_talwar]

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

[Anonymous]

Script attached

[sunny_talwar · Accepted solution]

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;

[Anonymous]

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

[Anonymous]

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

[sunny_talwar]

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