Coulmn with Max data value in load script

[poonam_kulwal]

Hi All,

I have sample data as shown below.

I am trying to achieve is to get last column shown as “Max Quty Sold”.

I have first 4 columns in my data set and I am looking for to generate last column as max of quty sold based on Product, Date and Category.

I want this kind of table on load script side so that I can use last column in my calculations across various charts.

Any help will be appreciated.

Thanks

Poonam

[sunny_talwar · Accepted solution]

Try this

Table:
LOAD Product, 
     Date, 
     Category, 
     [Quty sold]
FROM
[..\..\Downloads\Data_Sample.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join (Table)
LOAD Product,
	 Date,
	 Category,
	 Max([Quty sold]) as [Max Quty Sold]
Resident Table
Group By Product, Date, Category;

[vvira1316]

NoConcatenate
[DataTable]:
LOAD Product,
Date,
Category,
[Quty sold]
FROM
[Data_Sample.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join ([DataTable])
LOAD Product,
Date,
Category,
Max([Quty sold]) as [Max Qty Sold]
Resident [DataTable]
Group By Product,
Date,
Category

[poonam_kulwal]

Thank you Sunny!.

This is very helpful.

However there was also issue with my Date column (in my actual data set). Dates were not interpreted correctly. That leads to not getting desire output with  Group by clause and Left join. 

Issue is now resolved.

[sunny_talwar]

Super