Max Value in Load Script

[mapratt82]

I need to load the following with the Max EOD value for each Tax ID, help please:

LOAD

  "Tax_ID_Number" as "Tax ID",

    "EOD";

SQL SELECT "Tax_ID_Number",

    "EOD"

FROM "data".dbo."Source";

Thanks in advance!

[sunny_talwar · Accepted solution]

May be like this:

Table:

LOAD "Tax ID",

          Max(EOD) as EOD

Group By [Tax ID];

LOAD

  "Tax_ID_Number" as "Tax ID",

    "EOD";

SQL SELECT "Tax_ID_Number",

    "EOD"

FROM "data".dbo."Source";

[Anonymous]

Table:

LOAD

  "Tax_ID_Number" as "Tax ID",

    "EOD";

SQL SELECT "Tax_ID_Number",

    "EOD"

FROM "data".dbo."Source";

LEFT JOIN LOAD

     max(EOD) as MaxEOD,

     "Tax ID"

RESIDENT Table

GROUP BY "Tax ID";

[shraddha_g]

Try this.

Tax:

SQL SELECT "Tax_ID_Number",

EOD

From "data". "dbo". "source" ;

Final:

Load max(EOD)  as Max_EOD,

"Tax_ID_Number"

Resident Tax

Group by Tax_ID_Numbern

Drop table Tax;

[mapratt82]

This seems to be correct and the easiest fix. Thanks everyone!

[sunny_talwar]

Just another though, you might be able to do this in your SQL statement itself

Table:

SQL SELECT

     "Tax_ID_Number" as "Tax ID",

    Max("EOD") as EOD

FROM "data".dbo."Source"

GROUP BY "Tax_ID_Number;

[CreepyCatLady]

Wanted to thank you and give you kudos, Sunny. This helped me solve a problem that I've been trying to figure out for weeks. Thank you!!!

[EdLaw86]

I also have the same problem I want to get the max version of "Version No."  in my script and group by "No.". See my script:

Purchase_Order_Archieve:
LOAD
No. as "P.O. No.",
"Version No.",
"Order Date" as "Order Date PO Archieved",
"PR No." as "PR No. Created",
"Date Archived",
"Time Archived",
"Archived By",
//"Interaction Exist",
"Buy-from Vendor No.",
"Buy-from Vendor Name",
//"Vendor Authorization No.",
"PO Printing Date",
Amount,
"Amount Including VAT",
"Completely Received",
"Location Code" as "Location Code PO",
"Contractor's Rate",
"Expected Receipt Date",
Month(Date("Expected Receipt Date")) as "Month Expected Receipt Date",
Year(Date("Expected Receipt Date")) as "Year Expected Receipt Date",
"Requisition Date",
"Supplier Delivery Leadtime",
"Status" as "PO Status",
//"Delivery Terms 2",
Deleted
FROM [lib://AttachedFiles/Purchase Orders Archieves 08-16-2023 SC.xlsx]
(ooxml, embedded labels, table is [Purchase Order Archives]);