load expression

[jtrillori]

Mornig

I have a trouble withe a load script, maybe you can help me.

i have a invoice table, each invoice has a multiple departments and i want identify the department with the higher particaption on the invoice

INVOICE	DEPARTMENT	VALUE
INVOICE1	DEPARTMENT1	1000
INVOICE2	DEPARTMENT1	500
INVOICE1	DEPARTMENT2	800
INVOICE1	DEPARTMENT2	400
INVOICE1	DEPARTMENT3	12
INVOICE2	DEPARTMENT3	800
INVOICE3	DEPARTMENT3	200
INVOICE4	DEPARTMENT1	12
INVOICE3	DEPARTMENT1	400
INVOICE4	DEPARTMENT3	500

the result must be.

INVOICE1 = DEPARTMENT2 (800+400)

INVOICE2=  DEPARTMENT3 (800)

INVOICE3 = DEPARTMENT1 (400)

INVOICE4 = DEPARTMENT3 (500)

Can you help me??

thanks and regards

[jagan · Accepted solution]

Hi,

Please find attached file for solution.

Regards,

jagan.

[#3]

hi

try this

T:

LOAD INVOICE,

     DEPARTMENT,

     VALUE

FROM

(ooxml, embedded labels, table is Sheet1);

LOAD INVOICE,

    MAX(MAXVALUE) AS MAX,

    FirstSortedValue(DEPARTMENT,-MAXVALUE) as DEPARTMENT1

    Group by INVOICE;

T2:

LOAD INVOICE,

     DEPARTMENT ,

     SUM(VALUE) AS MAXVALUE

     Resident T

     Group BY DEPARTMENT ,INVOICE;

DROP Table T;

then output like this

INVOICE	DEPARTMENT1	MAX
INVOICE1	DEPARTMENT2	1200
INVOICE2	DEPARTMENT3	800
INVOICE3	DEPARTMENT1	400
INVOICE4	DEPARTMENT3	500

[jtrillori]

working nice with the two options.

thanks