Sort Order of Date Field.

[#1]

Dear All,

Greetings for the day.

Please suggest with proper code for below requirement.

I have Back Order Table with many fields. But for this requirement i require only five fields. Please find the attachments.

My Requirement :-

For the combination of (Part No (BO_ETD_SHIPP_PART_NO) , Cust Code (BO_ETD_CUST_CD) and Order No (BO_ETD_CUST_ORD_NO)) , there will be many creation date(BO_ETD_CRE_DATE) and etd's(BO_ETD_LATEST_ETD) (Estimated Time Delivery).

I want to create new sub fields as ETD1,ETD2,ETD3,ETD4,ETD5 AND ETDMAX.

For Eg:- Scenario 1) For this combination, if i have three etd's then ETD1,ETD2 and ETD3 only records. In this case no records will available for ETD4,ETD5 and ETDMAX.

Scenario 2) For this combination, if i have four etd's then ETD1, ETD2, ETD3 and ETD4 only records. In this case no records will available for ETD5 and ETDMAX.

Scenario 3) For this combination, if i have ten etd's then ETD1, ETD2, ETD3,ETD4,ETD5 and ETDMAX(Tenth ETD) (MAX is nothing but if there are more then five etd's then last etd is MAX etd. i.e. if no of etd's are more then five then only ETDMAX will come for that combination) only records. In this case all records will available.

Now i need to create sub fields (ETD1,ETD2,ETD3,ETD4,ETD5 and ETDMAX).

NOTE :- Here ETD field format is not date, its in number format. And suppose if user not entered any etd for that combination then system will automatically assign "99999999". In my code i am removing records where there is 99999999 for the first time . That means if 99999999 is available in between etd's then i am considering. If only 99999999 is available then i am neglecting.

This creation of sub fields should depends on creation date.

For eg :- In attachment ISSUE.QVW, For combination (8847447020,VE01A, TEO002246), there are three etd's. I am able to split it as ETD1,2 and 3. But the order is not coming as per my requirement. Order should be on creation date wise. In this eg, ETD1 is correct, but ETD2 should come as ETD3 and ETD3 should come as ETD2.

Here distinct etd's are 26.sep.2013,01.oct.2013,03.oct.2013. So i am getting ETD1 as 26.sep.2013, ETD2 as 01.oct.2013 and ETD3 as 03.oct.2013. But this is wrong . It should consider creation date. as per creation date ETD2 should be   03.oct.2013 and ETD3 should be 01.oct.2013.

In attachments i pasted both qvd and qvw. In QVW, code is available, Please suggest me to get my requirement properly.

Thank you.

[MK_QSL]

Attachment missing...!

[#3]

Hii...

SORRY..!!!

pls find the attached...

[CELAMBARASAN · Accepted solution]

Try like this

FINAL_ETD:
LOAD
  TEMP_CUST_ORD_NO,TEMP_CUST_CD,TEMP_SHIPP_PART_NO,
  TEMP_SHIPP_PART_NO&TEMP_CUST_ORD_NO&TEMP_CUST_CD AS %KEY_SHIP_BO_COM,
  IF(WildMatch(concat(DISTINCT BO_ETD_LATEST_ETD,','), '*99999999*'), '99999999') AS NOETD,
  Date(Date#(subfield(concat(DISTINCT(BO_ETD_LATEST_ETD),',',BO_ETD_CRE_DATE),',',1),'YYYYMMDD'))  AS ETD1,
    Date(Date#(subfield(concat(DISTINCT(BO_ETD_LATEST_ETD),',',BO_ETD_CRE_DATE),',',2),'YYYYMMDD'))  AS ETD2,
    Date(Date#(subfield(concat(DISTINCT(BO_ETD_LATEST_ETD),',',BO_ETD_CRE_DATE),',',3),'YYYYMMDD'))  AS ETD3,
    Date(Date#(subfield(concat(DISTINCT(BO_ETD_LATEST_ETD),',',BO_ETD_CRE_DATE),',',4),'YYYYMMDD'))  AS ETD4,
    Date(Date#(subfield(concat(DISTINCT(BO_ETD_LATEST_ETD),',',BO_ETD_CRE_DATE),',',5),'YYYYMMDD'))  AS ETD5,
    Date(Date#(IF(COUNT(DISTINCT BO_ETD_LATEST_ETD)>4, SubField(Concat(DISTINCT BO_ETD_LATEST_ETD,',',BO_ETD_CRE_DATE),',',COUNT(DISTINCT BO_ETD_LATEST_ETD))),'YYYYMMDD')) AS ETDMAX
Group by TEMP_SHIPP_PART_NO,TEMP_CUST_ORD_NO,TEMP_CUST_CD;
LOAD 
    BO_ETD_CUST_ORD_NO AS TEMP_CUST_ORD_NO,
    BO_ETD_CUST_CD AS TEMP_CUST_CD, 
    BO_ETD_SHIPP_PART_NO AS TEMP_SHIPP_PART_NO,
    MAX(BO_ETD_CRE_DATE) AS BO_ETD_CRE_DATE,
    BO_ETD_LATEST_ETD
Resident FINAL
group by BO_ETD_SHIPP_PART_NO,BO_ETD_CUST_ORD_NO,BO_ETD_CUST_CD, BO_ETD_LATEST_ETD;






DROP TABLE FINAL;

[#5]

Hii...

Thank Yu... it is working...