topic Re: Group By Clause with Order By Back end Scripting in QlikView

Group By Clause with Order By Back end Scripting

varunvarma — Mon, 02 Feb 2015 14:59:49 GMT

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.

Re: Group By Clause with Order By Back end Scripting

maxgro — Mon, 02 Feb 2015 15:12:17 GMT

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;

Re: Group By Clause with Order By Back end Scripting

varunvarma — Mon, 02 Feb 2015 15:33:51 GMT

Thank you Massimo.

Re: Group By Clause with Order By Back end Scripting

varunvarma — Mon, 02 Feb 2015 17:07:50 GMT

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.

Re: Group By Clause with Order By Back end Scripting

varunvarma — Mon, 02 Feb 2015 17:08:46 GMT

Dear San,

Please support. Thank you.

Re: Group By Clause with Order By Back end Scripting

maxgro — Mon, 02 Feb 2015 17:25:50 GMT

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;