Group By Clause with Order By Back end Scripting

[varunvarma]

Dear All,

Greetings for the day.

Please support with below requirement. Please find the attachment below.

REQUIREMENT :-

I have a table as per excel(Sheet tab name Data Base). I need to create a new table as follows.

I need to do "GROUP BY" with VIN_NO field, ORDER BY JOB_ORDER_DT DESC. For each VIN_NO I need to get first three rows as per JOB_ORDER_DT desc. Expected output should be given in same excel (Sheet tab name RESULT Required).

The above requirement, I need to do in back end scripting and need to create new table. Please let me know how to proceed. Thanks in advance.

[maxgro · Accepted solution]

RESULT:

SCRIPT:

tmp:

LOAD JOB_ORD_DT,

     BRC_CD,

     DLR_CD,

     TAX_INV_DT,

     VIN_NO

FROM

[Sort Vin.xls]

(biff, embedded labels, table is Database$);

final:

NoConcatenate

load

  * where id <= 3;

load

  *,

  if(VIN_NO<>Peek(VIN_NO),1,peek(id)+1) as id

Resident tmp

order by VIN_NO, JOB_ORD_DT desc

;

DROP Table tmp;

DROP field id;

[varunvarma]

Thank you Massimo.

[varunvarma]

Dear San,

Please find the attachments below. Addition to above requirement, now i need to create new fields as shown in QVW.

I need to create TEST!,TEST2 and TEST3 as shown in excel file (New Requirement tab). Please find the QVW. I am able to split the records but not in JOB_ORD_DT DESC wise. Please support. Thanks in advance.

[varunvarma]

Dear San,

Please support. Thank you.

[maxgro]

SCRIPT:

tmp:

LOAD JOB_ORD_DT,

     BRC_CD,

     DLR_CD,

     TAX_INV_DT,

     VIN_NO

FROM

[Sort Vin.xls]

(biff, embedded labels, table is Database$);

final:

NoConcatenate

load

  * where id <= 3;

load

  *,

  if(VIN_NO<>Peek(VIN_NO),1,peek(id)+1) as id

Resident tmp

order by VIN_NO, JOB_ORD_DT desc

;

DROP Table tmp;

// from here for new requirement,output in final2

let v='final2: ';

for i=1 to 3

       $(v)

       load

            VIN_NO,

            BRC_CD as test$(i)

       Resident

            final

       where

            id = $(i);

       let v='left join (final2) ';

next i;