Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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
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.
Super